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 -~----------~----~----~----~------~----~------~--~---