How to nest a join query result in a json array in laravel 5.2

I have a query with multiple joins

$review = DB::table('reviews')
            ->join('restaurants', 'reviews.restaurant_id', '=', 'restaurants.id')
            ->join('users', 'reviews.user_id', '=', 'users.id')
            ->leftjoin('reviewDetails', 'reviewDetails.review_id', '=', 'reviews.id')
            ->leftjoin('comments', 'comments.review_id', '=', 'reviews.id')
            ->select('reviews.review as Review', 'reviewDetails.caption as Caption','comments.comment as Comments' ,
                'restaurants.name as Restaurant', 'users.firstName as FName', 'users.lastName as LName')
            ->get();

and it is returning a dataset that looks like this.

[
  {
    "Review": "Review 1",
    "Caption": "Caption 1",
    "Comments": "Comment 1",
    "Restaurant": "Restaurant 1",
    "FName": "Reviewer1",
    "LName": "User"
  },
  {
    "Review": "Review 1",
    "Caption": "Caption 2",
    "Comments": "Comment 1",
    "Restaurant": "Restaurant 1",
    "FName": "Reviewer1",
    "LName": "User"
  },
  {
    "Review": "Review 1",
    "Caption": "Caption 1",
    "Comments": "Comment 2",
    "Restaurant": "Restaurant 1",
    "FName": "Reviewer1",
    "LName": "User"
  },
  {
    "Review": "Review 1",
    "Caption": "Caption 2",
    "Comments": "Comment 2",
    "Restaurant": "Restaurant 1",
    "FName": "Reviewer1",
    "LName": "User"
  },
  {
    "Review": "Review 2",
    "Caption": "Caption 3",
    "Comments": "",
    "Restaurant": "Restaurant 2",
    "FName": "John ",
    "LName": "Snow"
  },
  {
    "Review": "Review 2",
    "Caption": "Caption 4",
    "Comments": "Comment 4",
    "Restaurant": "Restaurant 2",
    "FName": "John ",
    "LName": "Snow"
  },
  {
    "Review": "Review 2",
    "Caption": "Caption 3",
    "Comments": "Comment 5",
    "Restaurant": "Restaurant 2",
    "FName": "John ",
    "LName": "Snow"
  },
  {
    "Review": "Review 2",
    "Caption": "Caption 4",
    "Comments": "Comment 5",
    "Restaurant": "Restaurant 2",
    "FName": "John ",
    "LName": "Snow"
  }
]

The dataset is correct but the formatting is wrong. As you can see, there's a lot of data repetition. I need the comments and captions to be nested in an array. Something like this:

[
  {
    "Review": "Review 1",
    "Caption": [
      {"caption":"Caption 1"},
      {"caption":"Caption 2"}
    ],
    "Comments": [
         {"comment":"Comment 1"},
         {"comment":"Comment 2"}
    ],
    "Restaurant": "Restaurant 1",
    "FName": "Reviewer1",
    "LName": "User"
  },

  {
    "Review": "Review 2",
    "Caption": [
      {"caption":"Caption 3"},
      {"caption":"Caption 4"}
    ],
    "Comments": [
      {"comment":"comment 4"},
      {"comment":"comment 5"}
    ],
    "Restaurant": "Restaurant 2",
    "FName": "John ",
    "LName": "Snow"
  }
]

I'm a beginner in Laravel and trying to learn querybuilder and eloquent. Is this something that can be done in laravel or do I have to write code to go through each element of the array and add elements to nested array?

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/29T7Ads
via IFTTT