CODEWITHSUNDEEP
mysqltags
DobotJr
DobotJr

Reputation: 4049

A better way to search for tags in mysql table

Say I have a table and one of the columns is titled tags with data that is comma separated like this.

"tag1,tag2,new york,tag4"

As you can see, some of tags will have spaces.

Whats the best or most accurate way of querying the table for any tags that are equal to "new york"?

In the past I've used:

SELECT id WHERE find_in_set('new york',tags) <> 0

But find_in_set does not work when the value has a space.

I'm currently using this:

SELECT id WHERE concat(',',tags,',') LIKE concat(',%new york%,')

But I'm not sure if this is the best approach.

How would you do it?

Upvotes: 2

Views: 5795

Answers (2)

Anthony Poon
Anthony Poon

Reputation: 887

When Item A can be associated with many of item B, and item B can be associated with many of item A. This is called Many to many relationship

Data with these relationship should be stored in separate table and join together only on query.

Examble

Table 1

| product_uid | price | amount |
|      1      | 12000 |  3000  |
|      2      | 30000 |   600  |

Table 2

| tag_uid | tag_value |
|    1    |   tag_01  |
|    2    |   tag_02  |
|    3    |   tag_03  |
|    4    |   tag_04  |

Then we use a join table to relate them

Table 3

| entry_uid | product_uid | tag_uid |
|    1      |     1       |     3   |
|    2      |     1       |     4   |
|    3      |     2       |     1   |
|    4      |     2       |     2   |
|    5      |     4       |     2   |

The query will be (If you want to select item one and the tag)

SELECT t1.*, t2.tag_value 
FROM Table1 as t1,
JOIN Table3 as join_table ON t1.product_uid = join_table.product_uid
JOIN Table2 as t2 ON t2.tag_uid = join_table.tag_uid
WHERE t1.product_uid = 1

Upvotes: 5

spencer7593
spencer7593

Reputation: 108370

If I needed to ignore the leading spaces before and after the commas in tags.

For example, if tags had a value of:

'atlanta,boston , chicago, los angeles  , new york '

and assuming spaces are the only character I want to ignore, and the tag I'm searching for doesn't have any leading or trailing spaces, then I'd likely use a regular expression. Something like this:

SELECT ...
  FROM t
 WHERE t.tags REGEXP CONCAT('^|, *', 'new york' ,' *,|$')

I recommend Bill Karwin's excellent book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming"

https://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557

Chapter 2 Jaywalking covers the antipattern of comma separated lists.

Upvotes: 2

Related Questions