CODEWITHSUNDEEP
mysqlspringjpaspring-boot
Pardha.Saradhi
Pardha.Saradhi

Reputation: 478

Querying on column having comma separated values

I have a 2 Mysql tables

  1. Category
id |  name 
-----------
 1 |  cat1
  1. Item
id |  name | cat_ids
--------------------
 1 |  prod1| 1,2,3

I have a use case where I get a category Id and I have to fetch all the related products.

I tried using like query. (If I give input as 1, it get products of not only 1 but also 11, 12... every number having 1 which is wrong)

I am using spring-boot + jpa in the project. I thought of doing this programmatically but there are thousands of records and iterating is not a feasible solution.

Please suggest

PS: I know this is a very bad design. But this was passed to me and I have to deal with this.

Upvotes: 0

Views: 1272

Answers (2)

osama yaccoub
osama yaccoub

Reputation: 1866

Assuming you don't have privilege to edit the db schema as mentioned by Martin Hennings .. u can use a condition : like '1,%' or like '%,1,%' or like '%,1' ... Assuming also that u have no spaces

Upvotes: 0

Martin Hennings
Martin Hennings

Reputation: 16846

This looks like a poor database design. You should have a third table category_item_relation or similar:

  1. Category_Item_Relation 
    id | cat_id | item_id
1  |      1 |       1
2  |      2 |       1
3  |      3 |       1
4  | ...

Then you can simply query with a JOIN:

SELECT * FROM Category
    JOIN Category_Item_Relation ON Category.id = cat_id
    JOIN Item ON Item.id = item_id
WHERE Category.id = ?

Upvotes: 2

Related Questions