Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hello,
I have 2 tables that I want to join by the user_id.
But I need to display in the frontend a table that shows rows by unique user ID, with data from the first table, and everything that matches the join from the second table to show as a list in the last column.
example data:
users:
user_id | name | email
--------|------------|----------------
5 | Jonny | [email protected]
comments:
user_id | comment | status
--------|-------------------|---------
5 | "Hello World" | active
5 | "Foo Bar" | active
Currently my join look like that:
Users::select('users.name', 'users.email', 'comments.comment')
->leftJoin('comments', function($join) {
$join->on('users.user_id', '=', 'comments.user_id')
->whereIn('status', ['active', 'under_review']);
})
So the join gets the correct data, But I need to group it by user id in the table in the front end and display it like so:
name | email | comments
-------|-------------------|----------------------
Jonny | [email protected] | "Hello World", "Foo Bar"
Before I added the join, I had a simple query that simply gets all the user details and display them (name and email in this case), and I simply iterated over the results because they were unique anyway.
but how can I iterate over the results now and leave the rows unique per user id?
You want GROUP_CONCAT to concatenate strings from a group
Users::selectRaw('users.name, users.email, GROUP_CONCAT(comments.comment) as comments')
->leftJoin('comments', function($join) {
$join->on('users.user_id', '=', 'comments.user_id')
->whereIn('status', ['active', 'under_review']);
})
->groupBy('comments.user_id')
->get();
You can achieve this differently.
User::
with('comments')
->get()
->map(function ($item, $key) {
return [
'name' => $item->name,
'email' => $item->email,
'comments' => implode(',', array_values($item->comments->only('comment'))),
];
}
);
@tykus @vincent15000 both are awesome, thanks! though I think the first one would be faster?
@Ligonsker yeah, doing the work on the database should be more performant.
Please or to participate in this conversation.