return related data as an array from sql (left outer) LARAVEL

Question

I have query this query that returns the related images to the comment

  return    $comments = \DB::table('comments')->select('comments.comment','comments.user_name','comments.shop_name',
            'comments.rating','comments.created_at','comments_images.images')->
        join('comments_images','comments.id','=','comments_images.comment_id','left outer')
        ->where('comments.product_id', '=', "11644254552")->
orderBy(\DB::raw('-`images`'), 'desc')->get();

it returns results like this

{

comment: "fdsfdsfsdffddsffds",
user_name: "ahmaf",
shop_name: "example.com",
rating: "4",
created_at: "2017-09-19 02:14:41",
images: https://example.com/kf/UT8.1.cX3XaXXagOFbX5.jpg
},
-{
comment: "fdsfdsfsdffddsffds",
user_name: "ahmaf",
shop_name: "example.com",
rating: "4",
created_at: "2017-09-19 02:14:41",
images: https://example.com/kf/UT81WzoX3BXXXagOFbXs.jpg
},
-{
comment: "sdfdsfdssfd",
user_name: "fdsfds",
shop_name: "example2.myshopify.com",
rating: "5",
created_at: "2017-09-19 02:12:25",
images: null
}

as you see the first two rows are duplicated and I want them to be returned like this

comment: "fdsfdsfsdffddsffds",
user_name: "ahmaf",
shop_name: "example.com",
rating: "4",
created_at: "2017-09-19 02:14:41",
images:{
 0: https://example.com/kf/UT8.1.cX3XaXXagOFbX5.jpg
 1: https://example.com/kf/UT81WzoX3BXXXagOFbXs.jpg
}
},

-{
comment: "sdfdsfdssfd",
user_name: "fdsfds",
shop_name: "example2.myshopify.com",
rating: "5",
created_at: "2017-09-19 02:12:25",
images: null
}

is that possible ? to return them to gather instead of make new row for each related data.

I tried with it worked, but I couldn't use orderBy images from comments_images table, it only works with columns from comments

$comments = Comment::where('product_id', '=', "11644215052")->with([
        'images' => function ($query)
        {
            $query->orderBy('images', 'desc');
        },
    ])->paginate(30);

groupBy won't work, if I do I will have to add all the columns inside it or it will return an error, so it won't return all the records. I just want to group the duplicated row together — Programming Skills, Sep 21 '17 at 15:31

score 0 · Answer 1 · answered Sep 21 '17 at 15:31

0

You are looking for a parent/child relationship result. You won't get that with a flat row result set. Look at using the laravel model with child relations.

https://laravel.com/docs/5.5/eloquent-relationships#querying-relations

answered Sep 21 '17 at 15:31

user3720435

1,421
1
17
27

I know all of it and I can do it using with (eager load) to return the data together ,but then I won't be able to use orderBy so I have to use joins, but I couldn't find a solution for this one without eager load – Programming Skills Sep 21 '17 at 15:36
I don't use Laravel 5, but I know you can set the relationship with ordering. https://stackoverflow.com/questions/18143061/laravel-orderby-on-a-relationship – user3720435 Sep 21 '17 at 15:39
you can, but not with (eager load) You can't order based on column from anther table. In my case I can't order them using column from comments_images table. – Programming Skills Sep 21 '17 at 15:52
With Laravel 4, I'm doing it. I haven't worked with 5 though. – user3720435 Sep 21 '17 at 15:53
Nice, I don't believe there's a big difference between two of them, so can you check this code for me, maybe I'm doing something wrong. I edited the question. – Programming Skills Sep 21 '17 at 15:59

return related data as an array from sql (left outer) LARAVEL

1 Answers1