Limiting a sql query result with join and conditions

Question

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...

[This post](https://stackoverflow.com/questions/13525656/limit-a-left-join-on-the-first-table) may help. — mseifert, Oct 10 '17 at 07:40
Thank you mseifert. I have looked this post, but it does not seems to solve my problem. The subquery used to limit results is what I use on my first query to get all medias, but it does not work for filtering results, as I am trying to limit table A, but my condition is on the joined table B. — Rylyn, Oct 10 '17 at 08:00
The idea with the concatenated tags is great and makes writing the query much easier. — Thorsten Kettner, Oct 10 '17 at 09:58

score 1 · Accepted Answer · answered Oct 10 '17 at 09:38

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;

score 0 · Answer 2 · answered Oct 10 '17 at 07:43

0

Are you expecting like this?

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 ? ...]
order by medias.id
limit 0,10

Here limit is used for first 10 record. you can use stored procedure for passing the two params of limit and pick the filtered result

answered Oct 10 '17 at 07:43

Newaz Sharif

424
4
12

Unfortunately no, I get 10 records, not 10 medias. As some medias have many tags, they are returned more than once in the result. Playing this request return 10 records, but only 4 medias. But thanks anyway ! – Rylyn Oct 10 '17 at 07:57
Can you share demo for your expected result ? – Newaz Sharif Oct 10 '17 at 08:00
1

Sure, I edited my original post to add expected result :) – Rylyn Oct 10 '17 at 08:09

Limiting a sql query result with join and conditions

2 Answers2