Order by matched records

Refresh

December 2018

Views

28 time

2

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:

  • meetup #2
  • meetup #1
  • meetup #4
  • meetup #16
  • meetup #5
  • meetup #7
  • meetup #8

2 answers

1

Для сортировки по количеству соответствующих категорий, вы могли бы group 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

Пример в SQL Fiddle.

2

Это, кажется, делать то, что вы хотите. Хотя последние две строки не может быть в порядке, вам требуется, но это потому, что значения для всех условий сортировки являются одинаковыми для этих двух строк:

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