Re: Preventing JOIN while checking if a self referencing FK is null

[diafygi]

Is there a reason why editor_id is meant to raise a field error?

-Daniel

On Mar 21, 12:05 pm, diafygi  wrote:
> >>> Blog.objects.filter(editor_id=None)
>
> FieldError: Cannot resolve keyword 'editor_id' into field.
>
> This was actually an offered answer in the previous thread, but the id
> version of the field still raises a field error.
>
> Daniel
>
> On Mar 21, 9:47 am, Javier Guerra Giraldez  wrote:
>
>
>
>
>
>
>
> > try:
>
> > > Blog.objects.filter(editor_id=None)
>
> > --
> > Javier

-- 
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: Preventing JOIN while checking if a self referencing FK is null

[Andre Terra]

Sorry. In the process of changing model names to fit my own installation
and test the code, I also managed to invert the relation.

Pay no attention to the man behind the curtains!

Cheers,
AT
On Mar 21, 2012 6:42 PM, "diafygi"  wrote:

> I cannot confirm that behavior. Can others verify?
>
> I'm using Django 1.3 and I get:
> >>> print Blog.objects.filter(editor=None).values('id').query
> SELECT `myapp_blog`.`id` FROM `myapp_blog` LEFT OUTER JOIN
> `myapp_user` ON (`myapp_blog`.`editor_id` = `myapp_user`.`id`) WHERE
> `myapp_user`.`id` IS NULL
>
> -Daniel
>
> On Mar 21, 2:07 pm, Andre Terra  wrote:
> > On Wed, Mar 21, 2012 at 5:41 AM, diafygi  wrote:
> > > >>> Blog.objects.filter(editor=None)
> >
> > >>> print Blog.objects.filter(editor=None).values('id').query
> >
> > SELECT "myapp_blog"."id" FROM "myapp_blog" WHERE "myappblog"."editor_id"
> IS
> > NULL
> >
> > Cheers,
> > AT
>
> --
> 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.
>
>

-- 
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: Preventing JOIN while checking if a self referencing FK is null

[diafygi]

I cannot confirm that behavior. Can others verify?

I'm using Django 1.3 and I get:
>>> print Blog.objects.filter(editor=None).values('id').query
SELECT `myapp_blog`.`id` FROM `myapp_blog` LEFT OUTER JOIN
`myapp_user` ON (`myapp_blog`.`editor_id` = `myapp_user`.`id`) WHERE
`myapp_user`.`id` IS NULL

-Daniel

On Mar 21, 2:07 pm, Andre Terra  wrote:
> On Wed, Mar 21, 2012 at 5:41 AM, diafygi  wrote:
> > >>> Blog.objects.filter(editor=None)
>
> >>> print Blog.objects.filter(editor=None).values('id').query
>
> SELECT "myapp_blog"."id" FROM "myapp_blog" WHERE "myappblog"."editor_id" IS
> NULL
>
> Cheers,
> AT

-- 
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: [bump] Preventing JOIN while checking if a self referencing FK is null

[Andre Terra]

Because django offers a nice ORM that makes it easy to do most queries,
especially one-liners like this one.

And refactoring raw SQL is a royal PITA.


Cheers,
AT

On Wed, Mar 21, 2012 at 3:14 PM, Python_Junkie <
software.buy.des...@gmail.com> wrote:

> Use straight sql.
>
> There are always potentially limitations with an abstraction such as the
> ORM.
>
> Why try to fit a square peg into a round whole.
>
>
> On Wednesday, March 21, 2012 4:41:32 AM UTC-4, diafygi wrote:
>>
>> There is an old thread that didn't end in a resolution about
>> preventing joins in a filter(foreign_key=None) scenario.
>>
>> http://groups.google.com/**group/django-users/browse_**
>> thread/thread/61ee2fb22deae326
>>
>> I'd like to bring it up again and ask if there is now a way to prevent
>> joins from a query.
>>
>> An example:
>> class Blog(model.Models):
>>editor = models.ForeignKey(User, null=True)
>>
>> # find all the blogs with no editors
>> >>> Blog.objects.filter(editor=**None)
>> DEBUG:django.db.backends:(0.**039) SELECT `myapp_blog`.`id` FROM
>> `myapp_blog` LEFT OUTER JOIN `myapp_user` ON (`myapp_blog`.`editor_id`
>> = `myapp_user`.`id`) WHERE `myapp_user`.`id` IS NULL;
>>
>> # Is there a way to do this query?
>> >>> Blog.objects.filter(editor=**None)
>> DEBUG:django.db.backends:(0.**039) SELECT `myapp_blog`.`id` FROM
>> `myapp_blog` WHERE `myapp_blog`.`editor_id` IS NULL;
>>
>> Thanks!
>> Daniel
>
>  --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/django-users/-/NfvXnYxnHBkJ.
>
> 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.
>

-- 
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: [bump] Preventing JOIN while checking if a self referencing FK is null

[Python_Junkie]

Use straight sql.

There are always potentially limitations with an abstraction such as the 
ORM.

Why try to fit a square peg into a round whole.

On Wednesday, March 21, 2012 4:41:32 AM UTC-4, diafygi wrote:
>
> There is an old thread that didn't end in a resolution about 
> preventing joins in a filter(foreign_key=None) scenario. 
>
>
> http://groups.google.com/group/django-users/browse_thread/thread/61ee2fb22deae326
>  
>
> I'd like to bring it up again and ask if there is now a way to prevent 
> joins from a query. 
>
> An example: 
> class Blog(model.Models): 
>editor = models.ForeignKey(User, null=True) 
>
> # find all the blogs with no editors 
> >>> Blog.objects.filter(editor=None) 
> DEBUG:django.db.backends:(0.039) SELECT `myapp_blog`.`id` FROM 
> `myapp_blog` LEFT OUTER JOIN `myapp_user` ON (`myapp_blog`.`editor_id` 
> = `myapp_user`.`id`) WHERE `myapp_user`.`id` IS NULL; 
>
> # Is there a way to do this query? 
> >>> Blog.objects.filter(editor=None) 
> DEBUG:django.db.backends:(0.039) SELECT `myapp_blog`.`id` FROM 
> `myapp_blog` WHERE `myapp_blog`.`editor_id` IS NULL; 
>
> Thanks! 
> Daniel

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/django-users/-/NfvXnYxnHBkJ.
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: [bump] Preventing JOIN while checking if a self referencing FK is null

[Andre Terra]

On Wed, Mar 21, 2012 at 5:41 AM, diafygi  wrote:

> >>> Blog.objects.filter(editor=None)
>


>>> print Blog.objects.filter(editor=None).values('id').query
SELECT "myapp_blog"."id" FROM "myapp_blog" WHERE "myappblog"."editor_id" IS
NULL


Cheers,
AT

-- 
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: Preventing JOIN while checking if a self referencing FK is null

[diafygi]

>>> Blog.objects.filter(editor_id=None)
FieldError: Cannot resolve keyword 'editor_id' into field.

This was actually an offered answer in the previous thread, but the id
version of the field still raises a field error.

Daniel

On Mar 21, 9:47 am, Javier Guerra Giraldez  wrote:
> try:
>
> > Blog.objects.filter(editor_id=None)
>
> --
> Javier

-- 
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: [bump] Preventing JOIN while checking if a self referencing FK is null

[Javier Guerra Giraldez]

try:

> Blog.objects.filter(editor_id=None)



-- 
Javier

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

[bump] Preventing JOIN while checking if a self referencing FK is null

[diafygi]

There is an old thread that didn't end in a resolution about
preventing joins in a filter(foreign_key=None) scenario.

http://groups.google.com/group/django-users/browse_thread/thread/61ee2fb22deae326

I'd like to bring it up again and ask if there is now a way to prevent
joins from a query.

An example:
class Blog(model.Models):
   editor = models.ForeignKey(User, null=True)

# find all the blogs with no editors
>>> Blog.objects.filter(editor=None)
DEBUG:django.db.backends:(0.039) SELECT `myapp_blog`.`id` FROM
`myapp_blog` LEFT OUTER JOIN `myapp_user` ON (`myapp_blog`.`editor_id`
= `myapp_user`.`id`) WHERE `myapp_user`.`id` IS NULL;

# Is there a way to do this query?
>>> Blog.objects.filter(editor=None)
DEBUG:django.db.backends:(0.039) SELECT `myapp_blog`.`id` FROM
`myapp_blog` WHERE `myapp_blog`.`editor_id` IS NULL;

Thanks!
Daniel

-- 
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: Preventing JOIN while checking if a self referencing FK is null

[John Boxall]

Thanks for the suggestion Daniel, but that doesn't help either :\

This will raise a field error (bff_id is not a valid field, only bff
is)
Person.objects.exclude(bff_id=None)

And these both result in JOINs:
Person.objects.exclude(bff=None)
Person.objects.exclude(bff__id=None)

Perhaps there is an optimization to be made at the Django ORM level?

jb

On Nov 4, 11:32 am, Daniel Roseman  wrote:
> On Nov 4, 7:22 pm, John Boxall  wrote:
>
> > Both of these will raise a FieldError:
>
> > people_with_bffs = Person.objects.filter(bbf_id__isnull=False)
> > people_with_bffs = Person.objects.filter(bbf_pk__isnull=False)
>
> > Cannot resolve keyword 'bbf_pk' into field. Choices are 'bbf'.
>
> > Thanks for the suggestion though!
>
> > jb
>
> Try Person.objects.exclude(bbf_id=None)
> --
> DR.
--~--~-~--~~~---~--~~
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: Preventing JOIN while checking if a self referencing FK is null

[Daniel Roseman]

On Nov 4, 7:22 pm, John Boxall  wrote:
> Both of these will raise a FieldError:
>
> people_with_bffs = Person.objects.filter(bbf_id__isnull=False)
> people_with_bffs = Person.objects.filter(bbf_pk__isnull=False)
>
> Cannot resolve keyword 'bbf_pk' into field. Choices are 'bbf'.
>
> Thanks for the suggestion though!
>
> jb
>

Try Person.objects.exclude(bbf_id=None)
--
DR.
--~--~-~--~~~---~--~~
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: Preventing JOIN while checking if a self referencing FK is null

[John Boxall]

Both of these will raise a FieldError:

people_with_bffs = Person.objects.filter(bbf_id__isnull=False)
people_with_bffs = Person.objects.filter(bbf_pk__isnull=False)

Cannot resolve keyword 'bbf_pk' into field. Choices are 'bbf'.

Thanks for the suggestion though!

jb

On Nov 4, 11:13 am, Javier Guerra  wrote:
> what about
>
> people_with_bffs = Person.objects.filter(bbf_id__isnull=False)
>
> ?
>
> (disclaimer, i haven't tried it)
>
> --
> Javier
--~--~-~--~~~---~--~~
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: Preventing JOIN while checking if a self referencing FK is null

[Javier Guerra]

what about

people_with_bffs = Person.objects.filter(bbf_id__isnull=False)

?

(disclaimer, i haven't tried it)

-- 
Javier

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

Preventing JOIN while checking if a self referencing FK is null

[John Boxall]

Hey Djangonauts,

I'd like to optimize the performance of a query I've got running on a
page - I've got a model which has a self referencing nullable foreign
key and I'd like to select all instances of that model that which have
a value for that column.

Imagine a class Person with a nullable FK to Person "Best Friends
Forever" - I'd like to select everyone who has a BFF! This is a
straight forward query operation and I would expect the ORM to avoid
using a JOIN to find it out - it would seem you could just check
whether the bff_id isnull - but in all the queries I've tried, the ORM
wants to do a JOIN on Person, which is expensive and unnecessary.

So my question is - how do you prevent a JOIN while checking if a self
referencing FK is null?

# Begin example codes #

class Person(model.Models):
   bbf = models.ForeignKey("self", null=True)

...

# Returns a queryset which causes a join
people_with_bffs = Person.objects.filter(bbf__isnull=False)

# Returns a queryset which causes a join
people_with_bffs = Person.objects.filter(bbf__pk__isnull=False)

# Returns a queryset which doesn't cause a join?
people_with_bffs = Person.objects.filter( ??? )



Cheers,

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