CODEWITHSUNDEEP
performancepostgresql
bl0b
bl0b

Reputation: 936

Order by matched records

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:

select 
*
from events
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:

Upvotes: 2

Views: 30

Answers (2)

user330315
user330315

Reputation:

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;

SQLFiddle: http://sqlfiddle.com/#!12/6c7a5/1

Upvotes: 2

Andomar
Andomar

Reputation: 238116

To sort by the number of matching categories, you could group by the event name, and sort by count(*) desc:

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.

Upvotes: 1

Related Questions