Level 102
Sounds like you want a join https://laravel.com/docs/9.x/queries#joins
Nov 21, 2022
10
Level 1
Implement `with` using query builder
I have tables with relationships like the following:
users:
id | name
1 | John
posts:
id | title | user_id
1 | Post 1 | 1
comments:
id | comment | user_id | post_id
1 | comment 1 | 1 | 1
I want to fetch the posts and comments related to a specific user using query builder without repeating the user data with every post, Just like how with works. So that posts would be an array:
DB::select("
SELECT id, name,
(SELECT id, title FROM posts WHERE user_id = 1),
(SELECT id, comment FROM comments WHERE user_id = 1)
FROM users
");
But this throws an error:
SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)
How to return posts as an array using query builder?
Level 1
If I use join, How would I select the sub-queries as an array?
I don't like using GROUP_CONCAT and then explode ..etc
Level 102
@mdev11 Then use eloquent. There is no such thing as an array in mysql
Level 1
Would it be fine to use it if there are +10 tables to get data from?
Level 102
@mdev11 Well if the data is limited (paginated for instance), 11 queries should be ok. But I dont see what you are trying to do here that wouldnt just work with group concat from the database. I assume you just want them comma separated on each row anyways?
Level 1
As I said there are multiple tables with one-to-many and many-to-many relations.
I fetch them to be displayed within a PDF. So for each GROUP_CONCAT I would have to explode and then loop ..etc
Level 102
@mdev11 Ok so its only for 1 "master" record. That should be fine.
Level 51
Why not using Eloquent Relationships?
$user = User::with('posts.comments')->where('id', 1)->first();
For Query Builder
DB::table('users')
->select('...') // Your selected columns
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->leftJoin('comments', 'users.id', '=', 'comments.user_id')
->get();
Level 1
1- Because I join +10 tables in my case. So using Eloquent and with wouldn't be good in this situation I think
2- With joins how to retrieve all posts and comments then? I mean I want to get posts and comments each separated as an array
Level 51
@mdev11 Run multiple queries.
$user = User::find(1);
$posts = Post::where('user_id', 1)->get();
$comments = Comment::where('user_id', 1)->get();
However, I don't see a problem running a relationship against 10+ tables if you need all this data at once.
Please or to participate in this conversation.