Using a filter with many to many relationship
Merric Mercer
model "TERMS".
Class OFFER:
terms=models.ManyToMany(Terms)
Assuming I have the following value for 3 records in Term
term1=2
term2=5
term3=8
I want to return ONLY those offers which have ALL three of these terms .
I've tried:
qs=Offer.objects.all().filter(terms=term1).filter(terms=term2).filter(terms=term3)
but this doesn't work - I always end up with an empty query set.
What am I missing?
Cheers
r_f_d
try
from django.db.models import Q (I think that is where it resides but
cannot verify right now, a quick check of the documentation should
confirm)
Offer.objects.filter(Q(terms__exact = 'term1') & Q(terms__exact =
'term2') & Q(terms__exact = 'term3))
merric
For example, I have two records which both share the term 'ThemePark",
one of these records also has the additional term "London".
However, when I run your suggestion I get an empty query set.
Cheers
r_f_d
What is the attribute of term that contains 'ThemePark' and 'London' ?
What are you trying to end up with, all records with themePark or only
records with themepark and London ?
-richard
merric
The attribute of Terms that contains 'ThemePark' and 'London is simply
'term'
The full model is:
class Terms(models.Model):
term=models.CharField(maxlength=100,core=True,help_text="search
term")
def __unicode__(self):
return self.search_term
class Offer(models.Model):
searchterms=models.ManyToManyField(Terms,blank=True)
Richard Dahl
Offer.objects.filter(Q(terms__term__exact = 'ThemePark') & Q(terms__term__exact = 'London'))
-richard
merric
think this is a failing which is particular to ManyToMany
relationships
On Sep 17, 4:33 pm, "Richard Dahl" <rich...@dahl.us> wrote:
> Ok, the query should then be:
>
> Offer.objects.filter(Q(terms__term__exact = 'ThemePark') &
> Q(terms__term__exact = 'London'))
>
> -richard
>
Richard Dahl
I am using the latest from svn and this does work for me, however I just looked at your model definitions again and realized that the query I sent you would not work. I didn't notice that in your Offer model the related name is 'searchterms', not 'terms', so if you did not catch that already try:
Offer.objects.filter(Q(searchterms__term__exact = 'ThemePark') & Q(searchterms__term__exact = 'London')) , although if it is returning an empty query set and not failing, you probably did catch my error.
If this does not work, have you tried instantiating the Terms objects and passing them to the query, (obviously not an efficient way of doing this but may provide some insight into whether or not the m2m query works), i.e.
t1 = Terms.objects.get(term__exact = 'ThemePark')
t2 = Terms.objects.get(term__exact = 'London')
Offer.objects.filter(Q(searchterms = t1) & Q(searchterms = t2))
-richard
peschler
Offer.objects.filter(searchterms__term='ThemePark',
searchterms__term='London')
According to the docs the filter parameters are AND'ed. Admittedly I
never used this in combination with ManyToMany fields, so I might be
wrong here.
peter
Tim Chase
>>
>> Offer.objects.filter(Q(terms__term__exact = 'ThemePark') &
>> Q(terms__term__exact = 'London'))
>
> think this is a failing which is particular to ManyToMany
> relationships
the problem is that the ORM maps the above to SQL that looks
something like
SELECT *
FROM app_offer o
INNER JOIN app_offer_term ot
ON o.id = ot.offer_id
INNER JOIN app_term t
ON ot.term_id = t.id
WHERE
t.term = 'ThemePark'
AND t.term = 'London'
That WHERE clause is where the problems originate. You end up
asking for "WHERE term = X and term = Y", but X != Y.
To get around this, you need to monkey a little under the covers
using an extra() call and do something like
offers = Offer.objects.all()
for term in ('ThemePark', 'London'):
offers = offers.extra(where=["""
EXISTS (
SELECT 0
FROM app_offer_term ot
ON o.id = ot.offer_id
INNER JOIN app_term t
ON ot.term_id = t.id
WHERE app_offer.id = ot.offer_id
AND t.term = %s
)"""], params=[term])
You'll have to adjust the table-names and field-names to
accomodate your own app/models, but I use this for doing what you
describe (AND queries across relations into the same table/row).
-tim
Tim Chase
>> for term in ('ThemePark', 'London'):
>> offers = offers.extra(where=["""
>> EXISTS (
>> SELECT 0
>> FROM app_offer_term ot
>> ON o.id = ot.offer_id
>> INNER JOIN app_term t
>> ON ot.term_id = t.id
>> WHERE app_offer.id = ot.offer_id
>> AND t.term = %s
>> )"""], params=[term])
>>
>
> - what does "SELECT 0" do?
"SELECT 0" just returns *something*. The EXISTS clause only
cares if any *rows* were returned, not what their content was.
It could just as easily have been "SELECT *" or "SELECT
'frobniculator'" or anything else. However, if the database
doesn't optimize the call, anything more than one column and
anything requiring a lookup into a table (such as a column value)
is extra effort/cycles. Thus, "SELECT 0" is a suggestion to the
database that if it has to bring something back, a single
non-calculated common constant will suffice. A minor
optimization, and I find it helpful as a reminder to myself that
I don't really care about the contents of the the SELECT
statement, just that *something* came back.
> - and "ON o.id = ot.offer_id"? Is it needed?
Hmm...looks like a bogus copy&paste on my part; a remnant of my
testing. With that line, it's bad SQL. It should just be
...
FROM app_offer_term ot
INNER JOIN app_term t
ON ot.term_id = t.id
...
-tim
PS: you sent your reply directly to me, rather than to the
mailing list. In this case, I could answer your question, but in
general, you may want to reply to the list so that others can
help too as it's pretty easy to hit the limits of my answers. :)
I've replied CC'ing the ML so that in case others have the same
questions you did, they can benefit from the answers too.
ecir hana
Thank you very much for the explanation!
I apologize for not sending this to the list as well, this was not intentional.