On Thu, 2009-01-29 at 06:44 -0800, phoebebright wrote:
> I want a distinct list of all the 'cat__names' that exist in
> Subcategory and have at least one entry in Business (Business is a
> subclass of model Directory which might be the problem)
>
> dir_query = Business.objects.all().select_related().distinct()
> ...
> subcats = dir_query.values('cat__name','cat').select_related().distinct
> ()
>
> But the SQL this generates has an additional field in the SELECT
> statement which means the DISTINCT does not have the effect I want and
> selects all entries.
>
> SELECT DISTINCT `town_subcategory`.`name`, `town_business`.`cat_id`,
> `town_directory`.`name`
> FROM `town_business`
> INNER JOIN `town_subcategory` ON (`town_business`.`cat_id` =
> `town_subcategory`.`id`)
> INNER JOIN `town_directory` ON (`town_business`.`directory_ptr_id` =
> `town_directory`.`id`)
> ORDER BY `town_directory`.`name` ASC
>
>
> Is there some way of forcing the fields in the select statement
> without having to write the whole SQL in raw format? Or another way
> of writing the query?
Even if you could do what you wanted here, it wouldn't solve your
problem. You're implicitly using the select fields and the inner join to
enforce the "a name exists" constraint. Django won't just add an
arbitrary table in via an extra inner join if it's not required.
There's a better solution to your problem, though. It's a little hard to
tell what the exact queryset will look like, since I don't understand
your models entirely, but this should be close. Firstly, you want
subcategory names, so that's the model to filter on. the constraint
"must have at least one business" is a filter on the business related
attribute being not-None:
SubCategory.objects.values('name').exclude(business=None)
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
-~----------~----~----~----~------~----~------~--~---
