Re: Can I control contents of SELECT in queryset?

[Malcolm Tredinnick]

On Mon, 2009-02-02 at 09:50 -0800, phoebebright wrote:
> After replicating you test and it still failing and various other
> experiments I downloaded the latest django trunk and it works fine!
> Always check the version first Phoebe!
> 
> Sorry for the run around.
> 
> For the record here are the two queries - correct one first, the
> difference is in the SELECT part of subquery, SELECT U1.`cat_id`
> instead of SELECT U0.`id`
> 
> SELECT `town_subcategory`.`name` FROM `town_subcategory` WHERE NOT
> (`town_subcategory`.`id` IN (SELECT U0.`id` FROM `town_subcategory`
> U0 LEFT OUTER JOIN `town_business` U1 ON (U0.`id` = U1.`cat_id`) WHERE
> U1.`directory_ptr_id` IS NULL))
> 
> SELECT `town_subcategory`.`name` FROM `town_subcategory` WHERE NOT
> (`town_subcategory`.`id` IN (SELECT U1.`cat_id` FROM
> `town_subcategory` U0 LEFT OUTER JOIN `town_business` U1 ON (U0.`id` =
> U1.`cat_id`) WHERE U1.`directory_ptr_id` IS NULL))

Oh, right. I fixed that a little while back and it's not in any release
yet. It will be in both Django 1.1 and 1.0.3.

Sorry -- I keep losing track of what bug fixes affect what areas at
times.

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

Re: Can I control contents of SELECT in queryset?

[phoebebright]

After replicating you test and it still failing and various other
experiments I downloaded the latest django trunk and it works fine!
Always check the version first Phoebe!

Sorry for the run around.

For the record here are the two queries - correct one first, the
difference is in the SELECT part of subquery, SELECT U1.`cat_id`
instead of SELECT U0.`id`

SELECT `town_subcategory`.`name` FROM `town_subcategory` WHERE NOT
(`town_subcategory`.`id` IN (SELECT U0.`id` FROM `town_subcategory`
U0 LEFT OUTER JOIN `town_business` U1 ON (U0.`id` = U1.`cat_id`) WHERE
U1.`directory_ptr_id` IS NULL))

SELECT `town_subcategory`.`name` FROM `town_subcategory` WHERE NOT
(`town_subcategory`.`id` IN (SELECT U1.`cat_id` FROM
`town_subcategory` U0 LEFT OUTER JOIN `town_business` U1 ON (U0.`id` =
U1.`cat_id`) WHERE U1.`directory_ptr_id` IS NULL))



On Jan 31, 1:50 am, Malcolm Tredinnick 
wrote:
> On Fri, 2009-01-30 at 07:46 -0800, phoebebright wrote:
> > Using your suggestion returns no values:
>
> Then there is something else going on in your code that is important and
> you haven't mentioned yet.
>
> If I use exactly the models you give:
>
>
>
> > class Category(models.Model):
> >     name = models.CharField(max_length=12, unique=True)
> >     description = models.TextField()
>
> > class Subcategory(models.Model):
> >     category = models.ForeignKey(Category)
> >     name =  models.CharField(max_length=30, unique=True)
>
> > class Directory(models.Model):
> >     name = models.CharField(max_length=60)
> >     phone = models.CharField(max_length=15)
>
> > class Business(Directory):
> >     cat = models.ForeignKey(Subcategory, limit_choices_to =
> > {'category__exact': 2})
>
> And I create a couple of categories, a few subcategories and a couple of
> businesses linked to some (but not all) of the subcategories, then the
> queryset I gave:
>
>         Subcategory.objects.values('name').exclude(business=None)
>
> returns exactly the right information (a non-empty collection of the
> subcategories related to businesses).
>
> To work out what is different in your situation, I suggest you start
> from that point, too. Copy and paste exactly what is above, create a few
> objects and try the queryset. Make sure you get back results (or we have
> to work out why you don't). Then work out what is different between that
> simpler example and your real code.
>
> For reference, the SQL query that is being generated for the above
> queryset is this:
>
> In [5]:
> models.Subcategory.objects.values('name').exclude(business=None).query.as_sql()
>
> Out[5]:
> ('SELECT`phoebe_subcategory`.`name` FROM `phoebe_subcategory` WHERE NOT
> (`phoebe_subcategory`.`id` IN (SELECTU0.`id` FROM `phoebe_subcategory`
> U0 LEFT OUTER JOIN `phoebe_business` U1 ON (U0.`id` = U1.`cat_id`) WHERE
> U1.`directory_ptr_id` IS NULL))',
>  ())
>
> (I happen to be using MySQL there, so the quoting of names will vary
> slightly on other databases, but it's the same query in all cases).
>
> 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
-~--~~~~--~~--~--~---

Re: Can I control contents of SELECT in queryset?

[Malcolm Tredinnick]

On Fri, 2009-01-30 at 07:46 -0800, phoebebright wrote:
> Using your suggestion returns no values:

Then there is something else going on in your code that is important and
you haven't mentioned yet.

If I use exactly the models you give:


> class Category(models.Model):
> name = models.CharField(max_length=12, unique=True)
> description = models.TextField()
> 
> 
> class Subcategory(models.Model):
> category = models.ForeignKey(Category)
> name =  models.CharField(max_length=30, unique=True)
> 
> 
> class Directory(models.Model):
> name = models.CharField(max_length=60)
> phone = models.CharField(max_length=15)
> 
> class Business(Directory):
> cat = models.ForeignKey(Subcategory, limit_choices_to =
> {'category__exact': 2})

And I create a couple of categories, a few subcategories and a couple of
businesses linked to some (but not all) of the subcategories, then the
queryset I gave:

Subcategory.objects.values('name').exclude(business=None)

returns exactly the right information (a non-empty collection of the
subcategories related to businesses).

To work out what is different in your situation, I suggest you start
from that point, too. Copy and paste exactly what is above, create a few
objects and try the queryset. Make sure you get back results (or we have
to work out why you don't). Then work out what is different between that
simpler example and your real code.

For reference, the SQL query that is being generated for the above
queryset is this:

In [5]:
models.Subcategory.objects.values('name').exclude(business=None).query.as_sql()

Out[5]: 
('SELECT `phoebe_subcategory`.`name` FROM `phoebe_subcategory` WHERE NOT
(`phoebe_subcategory`.`id` IN (SELECT U0.`id` FROM `phoebe_subcategory`
U0 LEFT OUTER JOIN `phoebe_business` U1 ON (U0.`id` = U1.`cat_id`) WHERE
U1.`directory_ptr_id` IS NULL))',
 ())

(I happen to be using MySQL there, so the quoting of names will vary
slightly on other databases, but it's the same query in all cases).

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

Re: Can I control contents of SELECT in queryset?

[phoebebright]

Using your suggestion returns no values:

In [9]: Subcategory.objects.values('name').exclude(business=None)
Out[9]: []

And there are subcategories:

In [10]: Subcategory.objects.values('name')
Out[10]: [{'name': u'Agricultural'}, {'name': u'Auctioneers & Estate
Agents'}, {'name': u'Construction & Maintenance'}, {'name': u'Dining &
Bars'}, {'name': u'Education & Childcare'}, {'name':
u'Entertainment'}, {'name': u'Financial & Business Services'},
{'name': u'Financial Banking  '}, {'name': u'Health & Beauty'},
{'name': u'Home & Garden'}, {'name': u'Hospitality'}, {'name':
u'Legal'}, {'name': u'Leisure'}, {'name': u'Manufacturing'}, {'name':
u'Miscellaneous'}, {'name': u'Motoring & Repairs'}, {'name':
u'Services'}, {'name': u'Shopping/Retail '}, {'name': u'Transport &
Logistics'}, {'name': u'Education '}, {'name': u'Transport'}, {'name':
u'Horse Riding'}, {'name': u'Walking'}]


And there are business objects with subcategories (naming here is
confusing!)

Business.objects.values('cat__name')
Out[7]: [{'cat__name': u'Agricultural'}, {'cat__name':
u'Agricultural'}, {'cat__name': u'Agricultural'}, {'cat__name':
u'Agricultural'}, {'cat__name': u'Agricultural'}, {'cat__name':
u'Agricultural'}, {'cat__name': u'Legal'}, {'cat__name': u'Shopping/
Retail '}, {'cat__name': u'Financial Banking  '}, {'cat__name':
u'Shopping/Retail '}, {'cat__name': u'Agricultural'}]

Also tried:
Subcategory.objects.values('name').exclude(directory=None)
FieldError: Cannot resolve keyword 'directory' into field. Choices
are: business, category, community, id, name, tourism



The problem could be that I have an additional level in that Business
is subcalssed to Directory like this:


class Category(models.Model):
name = models.CharField(max_length=12, unique=True)
description = models.TextField()


class Subcategory(models.Model):
category = models.ForeignKey(Category)
name =  models.CharField(max_length=30, unique=True)


class Directory(models.Model):
name = models.CharField(max_length=60)
phone = models.CharField(max_length=15)
...

class Business(Directory):
cat = models.ForeignKey(Subcategory, limit_choices_to =
{'category__exact': 2})

Any help much appreciated.  This is my second website in Django and
starting to become slightly competant, with much support from this
users group.  Thanks.

Phoebe.


On Jan 30, 2:23 am, Malcolm Tredinnick 
wrote:
> 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 theSELECT
> > statement which means the DISTINCT does not have the effect I want and
> > selects all entries.
>
> >SELECTDISTINCT `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 theselectstatement
> > 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 theselectfields 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
-~--~~~~--~~--~--~---

Re: Can I control contents of SELECT in queryset?

[Malcolm Tredinnick]

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

Re: Can I control contents of SELECT in queryset?

[phoebebright]

That's an interesting post but I havn't managed to get it to solve my
problem because I'm looking for a list of all categories and that
method using FOO_set only seems to work on a single object (get).

Also tried the raw sql method but I get an SQL error

(1064, "You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near ''business' td, town_subcategory sc WHERE td.cat_id = sc.id' at
line 1")

Even though if I run the sql manually it is fine.

from django.db import connection
cursor = connection.cursor()
cursor.execute("SELECT DISTINCT sc.name, sc.id FROM  town_%s td,
town_subcategory sc WHERE td.cat_id = sc.id", [for_cat])
subcats = cursor.fetchall()

Any further thoughts most welcome!

On Jan 29, 3:48 pm, Almost George 
wrote:
> On Jan 29, 8:44 am, 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 theSELECT
> > statement which means the DISTINCT does not have the effect I want and
> > selects all entries.
>
> >SELECTDISTINCT `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 theselectstatement
> > without having to write the whole SQL in raw format?  Or another way
> > of writing the query?
>
> Have you tried any of the methods listed in this 
> article?http://thisweekindjango.com/articles/2007/dec/21/retrieving-selective...
> ( Retrieving Selective Fields with Django )
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---

Re: Can I control contents of SELECT in queryset?

[Almost George]

On Jan 29, 8:44 am, 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?

Have you tried any of the methods listed in this article?
http://thisweekindjango.com/articles/2007/dec/21/retrieving-selective-fields-django/
( Retrieving Selective Fields with Django )
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---