Translating from Django ORM
Neil Blakey-Milner
We're currently translating an application from Django to TurboGears,
and using Elixir as a layer above SQLAlchemy for the database work.
I've managed to translate most of the queries thus far very easily,
but this particular case is proving pretty hard to do. Basically,
there are multiple joins on a single table, necessary to retrieve the
localised string for a particular string id, referenced in multiple
locations.
Here's the (simplified) Django model:
--------------------------------------------------
class Strids(models.Model):
txt = models.CharField(maxlength=500)
class Content(models.Model):
str = models.ForeignKey(Strids, db_column='strid')
locale = models.ForeignKey(Locales, db_column='localeid')
txt = models.CharField(maxlength=500)
class Dimensions(models.Model):
str = models.ForeignKey(Strids, db_column='strid',
related_name='Dimensions_txt_set')
helpstr = models.ForeignKey(Strids, db_column='helpstrid',
related_name='Dimensions__helptxt_set')
class Positionprofiles(models.Model):
jobtitle = models.ForeignKey(Strids, db_column='jobtitleid',
related_name='Positionprofiles_jobtitle_set')
status = models.ForeignKey(Statuslist, db_column='status', default=1)
joblevel = models.ForeignKey(Strids, db_column='joblevelid',
related_name='Positionprofiles_joblevel_set')
sector = models.ForeignKey(Dimensions, db_column='sectorid',
related_name='Positionprofiles_sector_set')
subsector = models.ForeignKey(Dimensions, db_column='subsectorid',
related_name='Positionprofiles_subsector_set')
domain = models.ForeignKey(Dimensions, db_column='domainid',
related_name='Positionprofiles_domain_set')
subdomain = models.ForeignKey(Dimensions, db_column='subdomainid',
related_name='Positionprofiles_subdomain_set')
--------------------------------------------------
I've translated these models to Elixir, which looks quite a lot nicer
to my mind:
--------------------------------------------------
class Strids(Entity):
has_field('txt', Unicode(500))
has_many('contents', of_kind='Content')
class Content(Entity):
has_field('txt', Unicode(500))
belongs_to('str', of_kind='Strids', colname="strid")
class Dimensions(Entity):
belongs_to('str', of_kind='Strids', colname='strid')
belongs_to('helpstr', of_kind='Strids', colname='helpstrid')
class Positionprofiles(Entity):
belongs_to('jobtitle', of_kind='Strids', colname='jobtitleid')
belongs_to('status_', of_kind='Statuslist', colname='status')
belongs_to('joblevel', of_kind='Strids', colname='joblevelid')
belongs_to('sector', of_kind='Dimensions', colname='sectorid')
belongs_to('subsector', of_kind='Dimensions', colname='subsectorid')
belongs_to('domain', of_kind='Dimensions', colname='domainid')
belongs_to('subdomain', of_kind='Dimensions', colname='subdomainid')
--------------------------------------------------
The has_many and belongs_to wording feels a bit weird in some cases,
but the result is working well for almost all queries.
The problem is the search builder, which takes a list of tuples of
search type and keyword, like:
[("joblevel", "senior"), ("jobtitle", "admin"), ("author", "kevin")]
The code looks something like this:
--------------------------------------------------
pp = Positionprofiles.object.all()
for searchtype, keyword in searchargs:
if not keyword.strip():
continue
if searchtype == 'jobtitle':
pp = pp.filter(jobtitle__content__txt__icontains = keyword)
elif searchtype == 'joblevel':
pp = pp.filter(joblevel__content__txt__icontains = keyword)
elif searchtype == 'occupation':
pp = pp.filter(Q(domain__str__content__txt__icontains = str(keyword)) |
Q(subdomain__str__content__txt__icontains =
str(keyword)))
elif searchtype == 'industry':
pp = pp.filter(Q(sector__str__content__txt__icontains = str(keyword)) |
Q(subsector__str__content__txt__icontains =
str(keyword)))
elif searchtype == 'author':
pp = pp.filter(Q(user__fullname__icontains = str(keyword)) |
Q(user__firstname__icontains = str(keyword)) |
Q(user__lastname__icontains = str(keyword)))
--------------------------------------------------
For a search of the above-mentioned list, it generates the following SQL:
--------------------------------------------------
SELECT "positionprofiles"."id","positionprofiles"."userid","positionprofiles"."groupid","positionprofiles"."patternid","positionprofiles"."jobtitleid","positionprofiles"."profilecopy","positionprofiles"."created","positionprofiles"."modified","positionprofiles"."status","positionprofiles"."joblevelid","positionprofiles"."sectorid","positionprofiles"."subsectorid","positionprofiles"."domainid","positionprofiles"."subdomainid"
FROM "positionprofiles" INNER JOIN "strids" AS
"positionprofiles__jobtitle" ON "positionprofiles"."jobtitleid" =
"positionprofiles__jobtitle"."id" INNER JOIN "content" AS
"positionprofiles__jobtitle__content" ON
"positionprofiles__jobtitle"."id" =
"positionprofiles__jobtitle__content"."strid" INNER JOIN "strids" AS
"positionprofiles__joblevel" ON "positionprofiles"."joblevelid" =
"positionprofiles__joblevel"."id" INNER JOIN "content" AS
"positionprofiles__joblevel__content" ON
"positionprofiles__joblevel"."id" =
"positionprofiles__joblevel__content"."strid" INNER JOIN "users" AS
"positionprofiles__user" ON "positionprofiles"."userid" =
"positionprofiles__user"."id" INNER JOIN "statuslist" AS
"positionprofiles__status" ON "positionprofiles"."status" =
"positionprofiles__status"."id" WHERE (("positionprofiles"."userid" =
1 OR "positionprofiles"."userid" = 2) AND
"positionprofiles__jobtitle__content"."txt" IS NOT NULL AND
"positionprofiles__jobtitle__content"."txt" ILIKE %admin% AND
"positionprofiles__joblevel__content"."txt" ILIKE %senior% AND
("positionprofiles__user"."fullname" ILIKE %kevin% OR
"positionprofiles__user"."firstname" ILIKE %kevin% OR
"positionprofiles__user"."lastname" ILIKE %kevin%) AND
"positionprofiles__status"."id" IN (2,4,5,6,7)) ORDER BY
"positionprofiles__jobtitle__content"."txt" ASC
--------------------------------------------------
I've been using join_to and join_via so far for joins. I guess I
would want an API something like this:
--------------------------------------------------
jt_content = Content.table.alias('jt_content')
jl_content = Content.table.alias('jl_content')
Positionprofiles.query().filter(and_(
Positionprofiles.join_to('user'),
or_(User.c.fullname.contains('kevin'),
User.c.firstname.contains('kevin'),
User.c.lastname.contains('kevin')),
Positionprofiles.join_via(['jobtitle', ('content', jt_content)]),
jt_content.c.txt.contains('admin'),
Positionprofiles.join_via(['joblevel', ('content', jl_content)]),
jl_content.c.txt.contains('senior')
))
--------------------------------------------------
(Once I figure out how to do it, adding 'filter' and 'filter_by' as
first-class methods on Entities would make my life much easier...)
Neil
--
Neil Blakey-Milner
http://nxsy.org/
n...@nxsy.org
Gaetan de Menten
>
> Hey there,
>
> We're currently translating an application from Django to TurboGears,
> and using Elixir as a layer above SQLAlchemy for the database work.
>
> I've managed to translate most of the queries thus far very easily,
> but this particular case is proving pretty hard to do. Basically,
> there are multiple joins on a single table, necessary to retrieve the
> localised string for a particular string id, referenced in multiple
> locations.
>
> Here's the (simplified) Django model:
> The has_many and belongs_to wording feels a bit weird in some cases,
> but the result is working well for almost all queries.
You could define aliases if you like:
one2many = has_many
many2one = belongs_to
or stuff like that...
> The problem is the search builder, which takes a list of tuples of
> search type and keyword, like:
>
> [("joblevel", "senior"), ("jobtitle", "admin"), ("author", "kevin")]
>
> The code looks something like this:
>
> --------------------------------------------------
>
>
>
> I've been using join_to and join_via so far for joins. I guess I
> would want an API something like this:
>
> --------------------------------------------------
>
> jt_content = Content.table.alias('jt_content')
> jl_content = Content.table.alias('jl_content')
>
> Positionprofiles.query().filter(and_(
> Positionprofiles.join_to('user'),
> or_(User.c.fullname.contains('kevin'),
> User.c.firstname.contains('kevin'),
> User.c.lastname.contains('kevin')),
> Positionprofiles.join_via(['jobtitle', ('content', jt_content)]),
> jt_content.c.txt.contains('admin'),
> Positionprofiles.join_via(['joblevel', ('content', jl_content)]),
> jl_content.c.txt.contains('senior')
> ))
This is really not Elixir-specific. I think it would make a nice
addition to SQLAlchemy, so please suggest this to the SQLAlchemy-list.
In the meantime, something like this should work:
jt_strids = Strids.table.alias('jt_strids')
jl_strids = Strids.table.alias('jl_strids')
jt_content = Content.table.alias('jt_content')
jl_content = Content.table.alias('jl_content')
join1 = Positionprofiles.join(jt_strids,
Positionprofiles.c.joblevel_id == jt_strids.c.id)
join2 = join1.join(jt_content, jt_content.c.str_id == jt_strids.c.id),
join3 = join2.join(jl_strids, ...)
join4 = join3.join(jl_content, ...)
Positionprofiles.query().select_from(join4).filter(
and_(
Positionprofiles.join_to('user'),
or_(
User.c.fullname.like('%kevin%'),
User.c.firstname.like('%kevin%'),
User.c.lastname.like('%kevin%')
),
jt_content.c.txt.like('%admin%'),
jl_content.c.txt.like('%senior%')
)
)
Btw: trimming your example to something as small as possible is a good
practice... Yours took me a while to understand, and the "user" stuff
for example has nothing to do with the problem here...
I guess you are aware of the fact this kind of query (the one from
django too) is very inefficient (it produces many duplicated rows --
one row per content but then really uses only one per
positionprofile).
--
Gaëtan de Menten
http://openhex.org
Neil Blakey-Milner
> This is really not Elixir-specific. I think it would make a nice
> addition to SQLAlchemy, so please suggest this to the SQLAlchemy-list.
Will do.
> In the meantime, something like this should work:
...
Thanks!
> I guess you are aware of the fact this kind of query (the one from
> django too) is very inefficient (it produces many duplicated rows --
> one row per content but then really uses only one per
> positionprofile).
Yeah - and it's not the worse in the application. But, for now, I'm
just making sure we use the same logic as the original code.