How to select all users in mySQL, along with their last three photos (or groups and the last three photos for each group)
preferably in a single query, in such a way as to get each row unique in the form
need to get
iduser nameuser ph1 ph2 ph3
from two tables
iuser nameuser
1 vasya
2 petya
id id_user ph_name
1 1 elephant
2 1 table
3 1 sofa
4 2 sky
5 3 water
6 2 sea
SELECT
alb.*,
us.username ,
us.id AS uid,
(SELECT ph.src FROM photo ph
WHERE ph.albumId=alb.id ORDER BY ph.id DESC LIMIT 1) AS pics,
(SELECT ph.src FROM photo ph
WHERE ph.albumId=alb.id ORDER BY ph.id DESC LIMIT 2,1) AS pic1,
(SELECT ph.src FROM photo ph
WHERE ph.albumId=alb.id ORDER BY ph.id DESC LIMIT 3,1) AS pic2,
(SELECT ph.src FROM photo ph
WHERE ph.albumId=alb.id ORDER BY ph.id DESC LIMIT 4,1) AS pic3,
FROM album alb
JOIN user us ON us.id=alb.userId
where LOWER(alb.name) RLIKE concat('[[:<:]]',?s,'')
AND ISNULL(alb.groupId)
but I think it's not quite optimal, maybe someone can suggest something better
if for a user we need the last 3 photos from each album
then it can be done like this
SELECT
us.*, alb.*, COUNT(ph.id) AS counter, GROUP_CONCAT(ph.src SEPARATOR ', ') AS f
FROM
user us
left JOIN album alb ON alb.userId = us.id
left JOIN photo ph ON ph.albumId = alb.id
GROUP BY
us.id
#HAVING counter <= 5
ORDER BY
us.id
DESC;
that is, in order to sort of return several columns as one row in mysql
you can use
GROUP_CONCAT - concatenation of group values
GROUP_CONCAT([DISTINCT] expr [, expr ...]
[ORDER BY {unsigned_integer|col_name|expr}
[ASC|DESC] [, col_name ...]]
[SEPARATOR str_val])
it's a shame there's no limit for GROUP_CONCAT
unfortunately, for some reason, when using GROUP_CONCAT and GROUP BY instead of nested SELECTs, even with grouping by nested fields, the query runs in 0.014 s, which is twice as slow as with the nested version, even though explain shows that it scans 4 times more records — maybe because there are still too few records; unfortunately I don't have time to check it right now
and the query is a bit more complex... but the idea is the same..
by the way, you can also use something like this construct
select ... concat(field1, ',', field2...)
from....
Comments