I have query that selects all users based on their sum of donation from another table and orders it by the sum of their donations.
But I would also like to select each user's last 2 comments where comment type is donation_comment by joining the them together with a space. And to be also able to search by user comments. If I specify where comment_text contains 'comment three' then only 1 entry would show for Sergey Brin.
I can't seem to figure out how to pull in their last comments and add where condition based on it.
So the result would be this
Array (  => stdClass Object ( [id] => 2 [username] => Sergey Brin [donation] => 500 [last_comments] => comment four comment three )  => stdClass Object ( [id] => 1 [username] => Larry Page [donation] => 400 [last_comments] => comment five comment two ) )
Here's my current query
SELECT users.id, users.username, sum(donations.donation) as donation from users inner join donations on users.id = donations.user_id where users.username like '%r%' group by users.id, users.username having sum(donations.donation) >= 400 order by donation desc
id | username | 1 Larry Page 2 Sergey Brin
id | user_id | donation | date | 1 1 100 2019-02-12 2 1 200 2019-02-13 3 2 500 2019-01-15 4 1 100 2019-04-10
id | user_id | comment_text | type | 1 1 comment one donation_comment 2 1 comment two donation_comment 3 2 comment three donation_comment 4 2 comment four donation_comment 5 1 comment five donation_comment