0

Here is my table:

+----+---------+------------+----------+
| id | message | projectID  | noteType |
+----+---------+------------+----------+
|  1 |       1 |    125 |   update |
|  2 |       2 |    125 |   update |
|  3 |       3 |    125 |   update |
|  4 |       4 |    125 |   update |
|  5 |       5 |    125 |   update |
|  6 |       6 |    125 |   update |

My query using the suggestion below:

SELECT `p`.`id`, `proName`, `p`.`proType`, `p`.`priority`, 
`p`.`busSegment`, `p`.`portfolio`, `p`.`description`, 
(SELECT group_concat('<li>', `message`, '</li>') AS temp FROM (SELECT 
projectID, message FROM notes where projectID = p.id AND noteType = 
'update' ORDER BY id DESC LIMIT 3) three_messages GROUP BY projectID) as 
updates
FROM `projects` as `p` 
WHERE `p`.`id` = 125

Error:

Error Code: 1054. Unknown column 'p.id' in 'where clause'

All records are returned. For some reason, the LIMIT 3 is not working.

The suggestion query work by it self.

SELECT group_concat('<li>', `message`, '</li>') AS updates
FROM (
    SELECT projectID, message
    FROM notes  where projectID = 125 AND noteType = 'update'
    ORDER BY id DESC
    LIMIT 3
) three_messages
GROUP BY projectID;

+---------------------+
| updates             |
+---------------------+
| 6,5,4               |
+---------------------+
user752746
  • 617
  • 9
  • 31
  • Another question, why are you rendering html markup out of sql? Do that in a serverside script instead? – gview May 27 '22 at 00:59

2 Answers2

1

LIMIT applies to the rows after grouping.

You can use it in a subquery and group the results of that subquery:

SELECT group_concat('<li>', `message`, '</li>' ORDER BY id DESC) AS updates
FROM (
    SELECT id, projectID, message
    FROM notes  where projectID = 125 AND noteType = 'update'
    ORDER BY id DESC
    LIMIT 3
) three_messages
GROUP BY projectID

It does look to me like you want:

group_concat('<li>', `message`, '</li>' ORDER BY id DESC SEPARATOR '')

though, to not put commas between the list items.

I also think it is a bad idea to use id for ordering; if you want newest notes, use a timestamp. Imagine if your database was hacked and you had to restore from a backup. After the restore, you start your system again and notes get added. Then you go through your application logs and recover some number of notes that were lost; if you are imputing order to ids, you have to increase all the ids added after the restore to make room for the recovered ones. It's much better just to be able to insert with a timestamp.

ysth
  • 96,171
  • 6
  • 121
  • 214
  • Thank you, appreciate your suggestion. This is exactly what I'm looking for. Cheers! – user752746 May 27 '22 at 16:26
  • I'm getting an error Unknown column 'p.id' in 'where clause' when I include your suggestion to my full query. Do you have any suggestion? – user752746 May 27 '22 at 18:13
  • oh, sorry, the subquery needs to select id too in my example, and likely in your full query; updated – ysth May 27 '22 at 18:37
  • are you in fact selecting just a single project? using limit will not work if you want up to three messages per project, see Bryan Dellinger's answer – ysth May 27 '22 at 18:39
  • No, I'm return one or more projects at a time, it depends of the user search criteria. I need to group_concat the messages for each project that's returned. thank you! – user752746 May 27 '22 at 18:53
  • then you need to use the other answer (which requires mysql 8 or mariadb 10.2+) – ysth May 27 '22 at 18:55
  • I found a work around. Thanks again for your help ysth. – user752746 May 27 '22 at 22:42
1

I'm guessing you have more than project id = 125 in your real query and for each one you want 3 results. in that case you may need to do some kind of ranking. I am doing it here with the row_number() function.

here is the fiddle https://www.db-fiddle.com/f/uqSzoth466RX86c5dCkfDR/0

with t as(select a.*,
row_number() over
(partition by project_id order by message desc) as rn
from mytable a
where note_type = 'update')
SELECT project_id,
group_concat('<li>', `message`, '</li>' ORDER BY id DESC) AS updates
from t where rn <=3 group by project_id;
Bryan Dellinger
  • 4,724
  • 7
  • 33
  • 79