0

Given this simple example (http://sqlfiddle.com/#!9/cab8cc);

The item has one or more colors.

Querying for all items having the color purple would retrieve itemA and itemB. (all items having at least color purple)

I'd be looking for a query that would return the items that have only the color purple. (or extensively, if an item has the colors purple & orange, and another one has the colors purple, orange and red, I'd like to be able to retrieve the items that have exactly the colors purple, orange and red)

is it something possible ?

given the need, and given the fact we're using web app framework (rails here), it boils down to the following implementation :

# use via : Item.by_colors(['Purple', 'Orange'])

model Item
  scope(:by_colors, ->(*args) do # args is an array of strings
    # ...dynamically build inner joins
    # ...count number of args
    # ...generate sql
    # ...execute sql through the orm
  end)
end

as the sql would be kind of unusual (at least to me), trying to make sure we're not using a bad table structure for this need

3
  • Something like this ? Commented Jun 2, 2023 at 14:12
  • @ErgestBasha tbh chatgpt told me to count too, I wanted to see if there was any other approaches
    – Ben
    Commented Jun 2, 2023 at 14:19
  • I don't think would be any simple answer than the one in the fiddle. Of course there might be other ways , but no need to overcomplicate the task. An index on id in table items and (item_id,color) in item_colors should help on performance Commented Jun 2, 2023 at 17:35

1 Answer 1

0

Ponder these:

SELECT i.id
FROM items AS i
WHERE ( SELECT GROUP_CONCAT(DISTINCT ic.color ORDER BY color) 
      FROM item_colors AS ic
      WHERE ic.item_id = i.id 
       AND ic.color IN ('Purple')
      ) = 'Purple';

SELECT item_id
    FROM item_colors
    WHERE color IN ('Purple', 'Orange')  -- the list
    GROUP BY item_id
    HAVING COUNT(DISTINCT color) = 2;   -- num items in list

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.