many-to-many query

[Nabuco]

Cheers everybody,

I have two tables (video, videokeyword) involved in a many2many
relationship which results in a 3rd table (video_keywords_list). A
video element can have many keyword elements, and otherwise, a keyword
element can have (be present) in many video elements.

Structure is:

app_video: id, ...(some more info not relevant here)
app_videokeyword: id, ...(same as above)
app_video_keywords_list: id, video_id, videokeyword_id

So I need to get the videos that match ALL the keywords that I provide
in the query, like "give me all the videos that contain the keywords
'horse', 'country' and 'green'". These videos may contain another
keywords or not, but I need them to contain at least ALL the keywords
provided.

Either solution (plain SQL soution or Django ORM solution) will be
fine for me. I know the filter_interface widget is doing some sort of
similar lookup whenever it is displayed in the admin, but my case has
little differences...

Thanks a lot,

hector

[Tim Chase]

> So I need to get the videos that match ALL the keywords that I provide
> in the query, like "give me all the videos that contain the keywords
> 'horse', 'country' and 'green'". These videos may contain another
> keywords or not, but I need them to contain at least ALL the keywords
> provided.
>
> Either solution (plain SQL soution or Django ORM solution) will be
> fine for me.

I've detailed my solution here:

http://groups.google.com/group/django-users/browse_thread/thread/24de9d4b74935296/ae401ce59ed5e931

which uses a call to extra() to do the needed selection.

It would look something like

keywords = ('horse', 'country' and 'green')
data = Video.objects.all()
for keyword in keywords:
data = data.extra(where=["""
NOT EXISTS (
SELECT 1
FROM app_video_keywords_list kwl
INNER JOIN app_videokeyword kw
ON kwl.videokeyword_id = kw.id
WHERE app_video.id = kwl.video_id
AND kw.keywordfield = %s
)"""],
params=[keyword])

(adjust the names of tables and fields accordingly, as I didn't
notice where you mention which field in app_videokeyword you're
searching on, so I made up "keywordfield")

You can also tweak the comparsion from "=" to ILIKE or however
you want to modify it.

-tim

[Nabuco]

Thanks Tim,

I canoot figure out why your code is not working for me... But I found
that

select vk.video_id, count(*) from dcrclips_video_keywords_list vk,
dcrclips_videokeyword k
where k.name in (keyword1,keyword2,etc...)
and vk.videokeyword_id = k.id
group by vk.video_id having count(*) = number of keywords

is also a solution. Just that raw SQL is never smart choice from the
coding point of view, but maybe because the database volume is big
trying to skip any programming workaround can boost the app speed...
But I am not sure yet, so I'll keep trying to use yours as well and
then compare both performances.

Cheers,

hector

On Sep 26, 7:15 pm, Tim Chase <django.us...@tim.thechases.com> wrote:
> > So I need to get the videos that match ALL the keywords that I provide
> > in the query, like "give me all the videos that contain the keywords
> > 'horse', 'country' and 'green'". These videos may contain another
> > keywords or not, but I need them to contain at least ALL the keywords
> > provided.
>
> > Either solution (plain SQL soution or Django ORM solution) will be
> > fine for me.
>
> I've detailed my solution here:
>

> http://groups.google.com/group/django-users/browse_thread/thread/24de...