First of, sorry for the title, I'm not sure how to name the question.
Basically, I'm writing a basic search engine based on a location and categories.
I have a table events, a table events_categories and a table categories. Object have a column city and a column state
One event have and belongs to many categories, and one category have and belongs to many events.
I currently have the following query:
LEFT OUTER JOIN "events_categories"
ON "events_categories"."event_id" = "events"."id"
LEFT OUTER JOIN "categories"
ON "categories"."id" = "events_categories"."category_id"
WHERE "categories"."id" = 2
OR "categories"."id" = 3
order by city!='Paris', state!='TX'
It works but I'd like to order my result based on the number of category matched and I also have duplicated row. I'd like to remove them
Here is the sqlfiddle I use
The expected result should be:
To sort by the number of matching categories, you could
group by the event name, and sort by
select e.name , count(*) from events e join events_categories ec on ec.event_id = e.id join categories c on c.id = ec.category_id where c.id in (2,3) group by e.name order by count(*) desc
Example at SQL Fiddle.
This seems to do what you want. Although the last two rows might not be in the order you require but that is because the values for all sort conditions are the same for those two rows:
select events.id as event_id, events.name as event_name, events.city, events.state, categories.id as category_id, categories.name as category_name, count(*) over (partition by events.id) as cat_count from events LEFT JOIN events_categories ON events_categories.event_id = events.id LEFT JOIN categories ON categories.id = events_categories.category_id WHERE categories.id IN (2,3) order by city <> 'Paris', state <> 'TX', cat_count desc;