OR lookups

[Julien]

Hi there,

I'd like to pull out all the projects that a given user is involved
in, either as a creator or as a participant of the projects:

class Project(models.Model):
name = models.CharField(max_length=200)
created_by = models.ForeignKey(User)

class Participant(models.Model):
project = models.ForeignKey(Project)
user = models.ForeignKey(User)

I'm running the following code:

q1 = Project.objects.filter(created_by=user)
q2 = Project.objects.filter(participants__user=user)
projects = q1 | q2

Unfortunately it doesn't return what I'd expect.
q1 and q2 work individually, but q1 | q2 returns a strange list of
projects. By "strange" I mean that I can't figure out what reasoning
is going on to provide that result.

Could you help me get the right code for what I want?

Many thanks!

Julien

[Malcolm Tredinnick]

What is the SQL statement being executed (have a look in the FAQ if you
don't know how to view the SQL statement).

There are some bugs in 'or' combinations. I don't know if you're hitting
one of those or not.

Regards,
Malcolm

--
Tolkien is hobbit-forming.
http://www.pointy-stick.com/blog/

[Julien]

Hi,

Here's the SQL code:

SELECT
`projects_project`.`id`,`projects_project`.`name`,`projects_project`.`created_by_id`,
FROM `projects_project` INNER JOIN `projects_participant` AS
`projects_project__participants`
ON `projects_project`.`id` =
`projects_project__participants`.`project_id`
WHERE ((`projects_project`.`created_by_id` = 1 OR
`projects_project__participants`.`user_id` = 1))

Any idea?

Thanks,

Julien

On Feb 11, 6:45 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:

[Malcolm Tredinnick]

On Mon, 2008-02-11 at 01:03 -0800, Julien wrote:
> Hi,
>
> Here's the SQL code:
>
> SELECT
> `projects_project`.`id`,`projects_project`.`name`,`projects_project`.`created_by_id`,
> FROM `projects_project` INNER JOIN `projects_participant` AS
> `projects_project__participants`
> ON `projects_project`.`id` =
> `projects_project__participants`.`project_id`
> WHERE ((`projects_project`.`created_by_id` = 1 OR
> `projects_project__participants`.`user_id` = 1))

Since the link from Project to Participants (in that direction) can be
empty, the join there should really be a LEFT OUTER join. This is one of
those bugs that occurs with "or" joins sometimes and is one of the
reason they've been heavily rewritten on the queryset-refactor branch.
Basically, if one of the querysets could be empty when there are results
in the other queryset, you sometimes see problems. This is one of those
cases.

All I can suggest is waiting a little while until queryset-refacotor is
merged into trunk (if you're really brave, you could try running against
the branch, but I haven't really declared it ready for testing yet). In
the meantime, pull the two querysets back into Python and merge the
lists there. Unless you're pulling back hundreds of thousands of
results, you won't notice any real speed difference.

Regards,
Malcolm

--
Depression is merely anger without enthusiasm.
http://www.pointy-stick.com/blog/

[Julien]

Thanks a lot for the tips! I think I'll go for the custom merging in
Python.

One stupid question. Is there any estimation as to when queryset-
refactor might be merged to trunk? I know it's hard to answer, but
should it be more like 1 month, 6 month, 1 year away? :)
The wiki says it's pretty unstable, so I guess it won't happen before
a while...

Thanks!



On Feb 12, 4:02 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:

[Malcolm Tredinnick]

On Tue, 2008-02-12 at 00:56 -0800, Julien wrote:
> Thanks a lot for the tips! I think I'll go for the custom merging in
> Python.
>
> One stupid question. Is there any estimation as to when queryset-
> refactor might be merged to trunk? I know it's hard to answer, but
> should it be more like 1 month, 6 month, 1 year away? :)
> The wiki says it's pretty unstable, so I guess it won't happen before
> a while...

Hopefully pretty soon. We're in the home stretch now and soon I'll be
asking for volunteers to test it.

Regards,
Malcolm

--
Why can't you be a non-conformist like everyone else?
http://www.pointy-stick.com/blog/

[jamie...@gmail.com]

Perhaps the current documentation should be updated to reflect that
this is not working. Its quite frustrating to have documentation that
describes something as working when it is not.

On Feb 12, 1:20 am, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:

[Malcolm Tredinnick]

On Wed, 2008-02-13 at 00:27 -0800, jamie...@gmail.com wrote:
> Perhaps the current documentation should be updated to reflect that
> this is not working. Its quite frustrating to have documentation that
> describes something as working when it is not.
>
> On Feb 12, 1:20 am, Malcolm Tredinnick <malc...@pointy-stick.com>
> wrote:
> > On Tue, 2008-02-12 at 00:56 -0800, Julien wrote:
> > > Thanks a lot for the tips! I think I'll go for the custom merging in
> > > Python.
> >
> > > One stupid question. Is there any estimation as to when queryset-
> > > refactor might be merged to trunk? I know it's hard to answer, but
> > > should it be more like 1 month, 6 month, 1 year away? :)
> > > The wiki says it's pretty unstable, so I guess it won't happen before
> > > a while...
> >
> > Hopefully pretty soon. We're in the home stretch now and soon I'll be
> > asking for volunteers to test it.

Since the majority of OR lookups work, this isn't worth doing. It's a
better use of our time to fix the problems.

Regards,
Malcolm

--
Honk if you love peace and quiet.
http://www.pointy-stick.com/blog/

[Julien]

Maybe a quick mention in the docs saying: "Warning, this might not
work in such and such case. But it is worked on and should be
eventually fixed, see the queryset-refactor branch."

I have to say that I spent quite some time trying to find out what was
wrong with my code, as I fully trusted the docs (and so should we). A
quick mention like that would have saved me quite some time and
frustration ;)

On Feb 13, 9:13 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:

[jamie...@gmail.com]

@Julien as did I.