On Fri, 2008-11-14 at 08:10 -0800, David Elias wrote:
> I'm using with Django 0.96 a custom Q object to make joins between
> models, here's an example:
>
> class Product(models.Model):
> group = models.IntegerField() # primary key
> code = models.CharField() # primary key
> name = models.CharField()
> ...
>
> class CartItem(models.Model):
> product_group = models.IntegerField() # product foreign key
> product_code = models.CharField() # product foreign key
> quantity = models.IntegerField()
> ...
>
>
> items = CartItem.objects.filter(
> QJoin(Product, 'p', Q(group='product_group',
> code='product_code'))).extra(
> select={
> 'p.name': 'product__name'
> })
>
> for this to work i've translated this:
> "Q(group='product_group', code='product_code')"
>
> into this:
> "ON (cartitem.product_group=p.group AND
> cartitem.product_code=p.code)"
>
> taking advantage of django.db.models.quey.Query._get_sql_clause
> method:
> if joins:
> sql.append(" ".join(["%s %s %s ON %s" % (join_type, table,
> alias, condition)
> for (alias, (table, join_type, condition))
> in joins.items()]))
>
> Now on the new django.db.models.sql.query.BaseQuery i can add new
> joins with the "join" method, but on "get_from_clause" the joins
> construction does not allow me to construct a join on multiple
> columns, here's the code from this method:
> if join_type and not first:
> result.append('%s %s%s ON (%s.%s = %s.%s)'
> % (join_type, qn(name), alias_str, qn(lhs),
> qn2(lhs_col), qn(alias), qn2(col)))
>
> The solution i can think of is to subclass de BaseQuery and overwrite
> the "get_from_clause" method.
It rapidly becomes almost unmanageable to put all join conditions into
the FROM clause, particularly once you start doing combinations with
both conjunctions and disjunctions. Fortunately, there is always a
logically equivalent query that doesn't use "ON", but uses "WHERE"
constraints. In fact, the database optimiser will convert the "ON"
constraints to equivalent WHERE constraints in most situations, since it
allows for more possibilities in join reordering. Django does similar
things internally and uses WHERE constraints predominantly.
At some point in the future, multi-valued ON constraints might make an
appearance (for example, multi-column primary key queries are possibly
easier to write), but they might not, too. The slight preference for
doing so at some point is that there are a couple of cases on some
databases where join constraints are handled more efficiently than where
constraints. That's really sub-optimal behaviour in the database
optimisers and it's sufficiently an edge case that we haven't done
anything about it yet, but it might happen one day in the future.
The easiest solution to your problem is to rewrite your query
construction to put the second constraint into the WHERE clause (via
add_filter(), for example). You might also need to add a comparison to
NULL if there's an outer join involved. See [1] -- a couple of
paragraphs into the section headed "The Where tree" -- for why that's
necessary.
[1] http://www.pointy-stick.com/blog/2008/03/11/queryset-implementation/
Regards,
Malcolm
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
