CODEWITHSUNDEEP
mysqlsql-order-by
LGT
LGT

Reputation: 5044

MySQL: Sort items by category count

I currently use the following query which works perfectly:

SELECT * FROM `items` WHERE `id` IN 
    (SELECT `item_id` FROM `categories_items` 
     WHERE `category_id` IN (1, 2) GROUP BY `item_id` 
     HAVING COUNT(`item_id`) = 2);

It selects all the items that are in all the selected (checkboxes) categories.

The problem is that most items are in many categories and a few items are only in two categories and when I only check those two, I still get a list of hundreds of items, making it nearly impossible to find the items that are in few categories.

My first idea was to add an ORDER BY "number_of_total_categories_that_the_selected_item_is_in" ASC somewhere in the query, but since I even got help with the current one and there would probably be a lot of calculations/subqueries for it to work, I thought of an extra column in the items table that would hold the number of categories it's in!

If not, all I can think of is to manually update a category_count column in items whenever it's needed.

Edit: Table field that holds row count from another table looks interesting, but I have no idea whether it would work in MySQL.

Upvotes: 0

Views: 301

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You want to use join rather than in. The following query filters for items that only have the two categories you want. It also counts the total number of categories, which can be used for the order by:

SELECT i.*
FROM `items` i JOIN
       (SELECT `item_id`, COUNT(*) as cnt
        FROM `categories_items` 
        WHERE `category_id` IN (1, 2)
        GROUP BY `item_id` 
        HAVING COUNT(DISTINCT CASE WHEN category_id IN (1, 2) THEN category_id END) = 2
       ) c
       ON i.id = c.item_id
ORDER BY cnt ASC;

EDIT:

If you want to count all the categories, then get rid of the where. It is not really doing anything:

SELECT i.*
FROM `items` i JOIN
       (SELECT `item_id`, COUNT(*) as cnt
        FROM `categories_items` 
        GROUP BY `item_id` 
        HAVING COUNT(DISTINCT CASE WHEN category_id IN (1, 2) THEN category_id END) = 2
       ) c
       ON i.id = c.item_id
ORDER BY cnt ASC;

Upvotes: 1

Related Questions