Laravel: Nesting query join results in a sub array

Question

NOTE Please do not suggest using Eloquent, this is specifically for the Laravel query builder.

For performance reasons we are using Query Builder to retrieve results from a table:

DB::table('posts')->get();

If we then want to join a relation onto that query:

DB:table('posts')
    ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
    ->get();

The results are merged into the array of each post:

[
    'id'                => 1,
    'title'             => 'My Blog Post',
    'content'           => '<h1>This is a post</h1><p>hello world</p>',
    'post_author'       => 'Billy',
    'comment'           => 'This is a comment',
    'comment_author'    => 'Andrew',
]

How can we have the joined results placed into a nested array? Such as:

[
    'id'                => 1,
    'title'             => 'My Blog Post',
    'content'           => '<h1>This is a post</h1><p>hello world</p>',
    'post_author'       => 'Billy',
    'comment'           => [
        'id'                => 22,
        'comment'           => 'This is a comment',
        'comment_author'    => 'Andrew',            
    ],
]

Relations are only used in case of Eloquent. Laravel Query Builder does not handle relations. @SougataBose — GiamPy
– GiamPy, Commented Jan 16, 2017 at 9:56
Are looking for some thing like GROUP BY in MySQL for grouping all the comments for each post ? — K Arun Singh
– K Arun Singh, Commented Jan 16, 2017 at 10:04
@SougataBose The question clearly states not to use Eloquent......... — AndrewMcLagan
– AndrewMcLagan, Commented Jan 16, 2017 at 10:10
In laravel you can't get above output using query builder. For this either you will need to write your own classes and functions which can then return your desired format of output. — Sandeep Garg
– Sandeep Garg, Commented Jan 16, 2017 at 10:12

Novocaine · Accepted Answer · 2019-06-14 21:12:29Z

3

Dont think its doable out of the box without Eloquent.

You can go the primitive route:

$results = DB:table('posts')
    ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
    ->select('posts.*', 'comments.*', 'comments.id as comments_id')
    ->get(); 

foreach($results as &$result) 
{ 
    $result['comment'] = [
        'id' => $result['comment_id'], 
        'comment' => $result['comment'], 
        'comment_author' => $result['comment_author']
    ]; 
    unset($result['comment_author'], $result['comment_id']);
}

edited Jun 14, 2019 at 21:12

Novocaine

4,8064 gold badges48 silver badges68 bronze badges

answered Jan 16, 2017 at 10:39

Paras

9,51336 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Murilo Over a year ago

I wish eloquent had a more subtle way of handling results, at least as an option.

guyaloni Over a year ago

Your solution does not group all comments of each post as a nested-array, actually each post will appear N times, when N is its comments number.

Jagadish Meghval Over a year ago

This will make the query very slower, as if there are more than 10000+ post, then query itself will take some seconds to executes

Paras Over a year ago

@JagadishMeghval the question was how to get join results in a nested manner. It has nothing to do with performance.

Kabelo2ka Over a year ago

Does eloquent the same as on this answer?

guyaloni · Accepted Answer · 2021-04-26 20:23:33Z

Since you work with DB facade and not Eloquent, and cannot use built-in with() method, you have to implement it yourself:

$posts = DB::table('posts')->get()->toArray();
$comments = DB::table('comments')->get()->toArray();

foreach($posts as &$post)
{
    $post->comments = array_filter($comments, function($comment) use ($post) {
        return $comment->post_id === $post->id;
    });
}

return $posts;

If you want to get rid of post_id for comments entries, you can do:

$posts = DB::table('posts')->get()->toArray();
$comments = DB::table('comments')->get()->toArray();

foreach($posts as &$post)
{
    $comments = array_filter($comments, function($comment) use ($post) {
        return $comment->post_id === $post->id;
    });

    $post->comments = array_map(function ($comment) {
        unset($comment->id);
        return $comment;
    }, $comments);
}

return $posts;

(I guess the runtime would be similar to with(), since after-all MySql does not provide this functionality out-of-the-box).

Guest2 · Accepted Answer · 2022-03-28 14:29:30Z

0

Here some new information:

$data= $DB->select("select posts.*,comments from posts left join comments on posts.id = comments.post_id");

I am not sure it is work or not but you can try

edited Mar 28, 2022 at 14:29

answered Mar 28, 2022 at 11:28

Guest2

11 bronze badge

1 Comment

Sayalic Over a year ago

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Guest2 · Accepted Answer · 2022-03-28 16:49:01Z

0

You can try with this

$data= $DB->select("select *,(select json_agg(datas) from (select * from comments where posts.id=comments.post_id) as datas) as comments from posts;");

But you may need to decode at comments as well

answered Mar 28, 2022 at 16:49

Guest2

1

Comments

Adeel Empire · Accepted Answer · 2023-06-27 10:39:28Z

0

$products = Category::with('product')->get(array('category.qty','category.product_id','category.net_unit_price as price'));

    foreach ($products as $p){
        $pl['name'] = $p->product->name;
        $pl['image'] = $p->product->image;
        unset($pl->product);
    }

edited Jun 27, 2023 at 10:39

answered Jun 27, 2023 at 10:37

Adeel Empire

538 bronze badges

Collectives™ on Stack Overflow

Laravel: Nesting query join results in a sub array

5 Answers 5

5 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related