CODEWITHSUNDEEP
mysqlsqltags
Marcin Roguski
Marcin Roguski

Reputation: 13

SQL query to select result based on data from column in multiple rows - phrase search based on tags

So I have a little image gallery that I started to enhance using tags. I decided I go with simplest solution and I have a table just like:

describe photo_tags;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| photoid | bigint(20)  | NO   | PRI | NULL    |       |
| tag     | varchar(32) | NO   | PRI | NULL    |       |
+---------+-------------+------+-----+---------+-------+

It works, I have unique index for the photoid,tag pair to avoid duplicates and generally it does what's expected, spare for one annoying thing: I want to be able to search not just by a single tag, but a phrase.

The query (example below) is generated by PHP based on sanitized query string treated with str_word_count.

An example, here's a snippet from actual entries in DB

+---------+-----------------------+
| photoid | tag                   |
+---------+-----------------------+
|    8717 | red                   |
|    8717 | road                  |
|    8717 | sky                   |
|    8717 | tanker                |
|    8717 | trees                 |
|    8717 | truck                 |
|    8717 | truck on truck action |
|    8717 | vehicle               |
|   18858 | clouds                |
|   18858 | green                 |
|   18858 | park                  |
|   18858 | sky                   |
|   18858 | trees                 |
|   18858 | truck                 |
|   18858 | vehicle               |
|   18858 | walkway               |
+---------+-----------------------+

Say I want to search the gallery based on tag "red truck":

This will not work, obviously

select photoid from photo_tags where tag="red truck" or (tag="red" and tag="truck");

This will sort of work:

select photoid from photo_tags where tag="red truck" or tag in('red','truck');

but it will basically select photoid that obviously have red or truck, not necessarily both of them.

Does anyone have idea how to improve the query so without modifying underlying table. Or maybe there's another way to achieve what I'm trying to do? I'm using MariaDB 10.3 and PHP 7.3 (basically what comes in Debian 10)

Upvotes: 1

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think you want aggregation:

select photoid
from photo_tags 
where tag in ('red', 'truck')
group by photoid
having count(*) = 2;

If you can have 'red truck' as well, then:

select photoid
from photo_tags 
group by photoid
having sum(tag in ('red', 'truck')) = 2 or
       sum(tag = 'red truck') > 0;

Upvotes: 2

Related Questions