I am writting a simple application that is ordering my medias (pictures, music, videos...). Each media can ben associated with 0 to many tags. My goal is to have a UI where I can search my medias (for exemple, show images and videos tagged like %hol%, and return both holidays tagged photos and hollywood tagged photos).
Here's my database :
Table medias
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| path | varchar(400) | NO | UNI | NULL | |
| type | varchar(5) | NO | | NULL | |
| libelle | varchar(200) | NO | | NULL | |
| ratings | int(2) | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+
Table tags
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| libelle | varchar(200) | NO | UNI | NULL | |
+---------+--------------+------+-----+---------+----------------+
Table medias_tags
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id_media | int(11) | NO | PRI | NULL | |
| id_tag | int(11) | NO | PRI | NULL | |
+----------+---------+------+-----+---------+-------+
As I have many medias, I had to limit the result. So in my front-end, I made a pagination system, and query my medias according to the page I am (for exemple, if I am on page 3, I put LIMIT 20 OFFSET 60 in my sql statement).
Now I'm trying to filter my medias. I have a searchbar, and if I type 'hol', I want to get 20 medias with tagged like '%hol%' (holidays, hollywood...)
Getting filtered medias works, but I don't know how to get exactly 20 medias.
Here's my sql query without filtering:
SELECT
medias.id, medias.path, medias.type, medias.libelle as libelle, medias.ratings, tags.libelle as tag
FROM (select * from medias LIMIT ? OFFSET ?) medias
left outer join medias_tags on medias.id = medias_tags.id_media
left outer join tags on tags.id = medias_tags.id_tag
And here's my filtering sql query:
SELECT
medias.id, medias.path, medias.type, medias.libelle as libelle, medias.ratings, tags.libelle as tag
FROM medias
left outer join medias_tags on medias.id = medias_tags.id_media
left outer join tags on tags.id = medias_tags.id_tag
WHERE tags.libelle LIKE ? [OR tags.libelle LIKE ? ...]
(last parameters are my tags)
Both query work well, but I can't find a way to limit my filtered result. Here's a sample of my filtering query result :
+----+-------------+-------+-------------------+---------+------------+
| id | path | type | libelle | ratings | tag |
+----+-------------+-------+-------------------+---------+------------+
| 11 | mock/02.jpg | PHOTO | 02.jpg | 0 | dark |
| 1 | mock/03.jpg | PHOTO | Purple | 5 | wallpapper |
| 3 | mock/01.jpg | PHOTO | Wave | 5 | wave |
| 3 | mock/01.jpg | PHOTO | Wave | 5 | wallpapper |
+----+-------------+-------+-------------------+---------+------------+
How can I limit my filtering result to only return n different medias id ? Is there a pure sql solution ? Maybe with stored procedures ?
Thanks !
EDIT :
Here's a result I'd like with limit = 7 :
+----+-------------+-------+-------------------+---------+------------+
| id | path | type | libelle | ratings | tag |
+----+-------------+-------+-------------------+---------+------------+
| 11 | mock/02.jpg | PHOTO | 02.jpg | 0 | dark |
| 7 | mock/01.jpg | PHOTO | NEWLY ADDED MEDIA | 8 | wallpapper |
| 2 | mock/02.jpg | PHOTO | Night | 5 | wallpapper |
| 2 | mock/02.jpg | PHOTO | Night | 5 | dark |
| 1 | mock/03.jpg | PHOTO | Purple | 5 | wallpapper |
| 4 | mock/03.jpg | PHOTO | Purple 2 | 5 | wallpapper |
| 5 | mock/03.jpg | PHOTO | Purple 3 EDITED | 8 | wallpapper |
| 3 | mock/01.jpg | PHOTO | Wave | 5 | wave |
| 3 | mock/01.jpg | PHOTO | Wave | 5 | wallpapper |
+----+-------------+-------+-------------------+---------+------------+
I have 9 rows, but only 7 distincts media id. Every media has a tag like '%a%'.
EDIT 2 : someone posted an answer, but deleted it. His idea was to concatenate tags, which would be a nice solution too.
Something like that :
+----+-------------+-------+-------------------+---------+------------+
| id | path | type | libelle | ratings | tag |
+----+-------------+-------+-------------------+---------+------------+
| 11 | mock/02.jpg | PHOTO | 02.jpg | 0 | dark |
| 7 | mock/01.jpg | PHOTO | NEWLY ADDED MEDIA | 8 | wallpapper |
| 2 | mock/02.jpg | PHOTO | Night | 5 | wallpapper, dark |
| 1 | mock/03.jpg | PHOTO | Purple | 5 | wallpapper |
| 4 | mock/03.jpg | PHOTO | Purple 2 | 5 | wallpapper |
| 5 | mock/03.jpg | PHOTO | Purple 3 EDITED | 8 | wallpapper |
| 3 | mock/01.jpg | PHOTO | Wave | 5 | wave, wallpapper |
+----+-------------+-------+-------------------+---------+------------+
But I have no idea how to write this sql query...
Use GROUP_CONCAT in order to build a tag string per media and outer join this result. Then apply your limit clause as desired
select
medias.id,
medias.path,
medias.type,
medias.libelle,
medias.ratings,
mtags.tags
from medias
left outer join
(
select id_media, group_concat(tags.libelle order by tags.libelle) as tags
from medias_tags
join tags on tags.id = medias_tags.id_tag
group by id_media
) mtags on mtags.id_media = medias.id
order by medias.id
limit 20 offset 60;