On Tue, 2009-08-04 at 18:41 -0700, Daybreaker wrote:
> See a recent thread here:
>
> http://groups.google.com/group/django-users/browse_thread/thread/0ee03167b7b5e873/850ac8304bf952b4#850ac8304bf952b4
>
> and this query:
>
> mymodel.objects.extra(select={'ordering_field': 'IF(STRCMP
> (bbs_tag.name, 'notice'), '',bbs_tag.name)'},
> order_by=['ordering_field'])
>
> bbs_tag (=Tag._meta.db_table) should exist in the queryset, so I added
> a filter condition: tags__name__contains='' to make Django to perform
> a join.
Urgh. Not pretty or particularly robust. It would be entirely fine for
Django to hypothetically optimise away that filter condition, for
example (it doesn't now, but that's not to say it will never do so).
The extra() method already takes "tables" and "where" parameters, so you
can use those to set up the join in a more self-contained fashion (all
the custom stuff is in extra()).
Or look into writing a custom Q-like filter. That isn't documented,
since it's not really public API, but it's fairly easy to work out from
starting at django.db.models.sql.query.Query.add_q() to see what the API
looks like and it isn't going to change significantly in the near future
-- in fact, it will probably become public API pretty soon by virtue of
getting documented. We have enough experience now to know how that
particular API shoudl work.
>
> Article.objects.filter(belongs_to=board, tags__name__contains='').extra
> (
> select={
> '_ordering':"CASE bbs_tag.name WHEN 'notice' THEN '' END",
> 'is_notice2':"bbs_tag.name = 'notice'",
> },
> order_by=['-_ordering', '-written_at']
>
> But this query produces 7 objects with one duplicated.
> (Note that IF clause is a mysql-specific extension, so it's better to
> use CASE clause that is standard.)
>
> The result set is a list of Article instances, but inside the query,
> tags should be joined and processed.
> How do I do this in more clear way?
Write the SQL directly. If you want ultimate control over the SQL, then
write SQL. If you want to and can express your query using the Python
models and other Python objects, use Django's ORM. However, the ORM is
not intended to be a 100% replacement for SQL; we already have SQL for
that. In particular, the ORM does not work in terms of specifying joins
between tables. It allows you to follow relations between models, which
translates to joins (sometimes), but it's not a one-to-one relationship.
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
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
