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 <[EMAIL PROTECTED]> 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... > > 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 --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
