1

I have a table in a MySQL database which relates items and tags, with columns item_id, tag_id. I need to write a query which receives a list of items and a list of tags and applies each tag to each item.

My current implementation looks like this:

INSERT INTO item_tag (item_id, tag_id) 
  (SELECT tag.tag_id, item.item_id FROM tag, item
  WHERE tag.tag_id in (:tags) 
   AND  item.item_id in (:items))

And this works but it's hacky and inneficient, I'm querying the tag and item tables to fetch values I already have in the parameters (and I already know at this point those are valid ids, so I don't really need to check for existence).

Is there a cleaner way to do this? For example I think of something like (psuedo code):

SELECT * FROM all_values_in_a_column(:tags) 
JOIN all_values_in_a_column(:items)
jspurim
  • 925
  • 8
  • 25
  • 1
    Essentially an implied `CROSS JOIN`? – kchason Nov 08 '17 at 14:14
  • Yes, a cross join, but over the values in the parameters – jspurim Nov 08 '17 at 14:15
  • So you have two lists and you want the combinations of them? Any reason you don't just do it in whichever scripting language you're using? – kchason Nov 08 '17 at 14:45
  • I could do it at application level too, but then I would end up sending a really long query to the DBMS. I'll probably go with that option if there is no easy way to implement it in SQL. – jspurim Nov 08 '17 at 14:57
  • 1
    MySQL doesn't seem to have an easy way of doing this... here might be a good start, but probably not worth the overhead, you'd need to benchmark this: https://stackoverflow.com/questions/11835155/mysql-split-comma-separated-string-into-temp-table – kchason Nov 08 '17 at 16:16
  • I finally solved it at application level. Thanks for the help anyway. I'll leave the question open in case someone finds a way to do this in SQL. – jspurim Nov 08 '17 at 18:28

0 Answers0