How can I group select values in a single select as array in laravel mysql?

Question

I am trying to get this from a query using eloquent.

$data = [
    "user" => [
        "img_profile" => "profileimage",
        "username" => "myusername"
    ],
    "description" => "postdescription",
    "img" => "postimg"
];

I managed to get this using the following php code, but I want to get this from the query, is there any way?

$posts = posts::join('business', 'posts.user_id', '=', 'business.user_id')
    ->join('cities', 'business.city_id', '=', 'cities.id')
    ->select(
        'posts.description as description',
        'posts.img as img',
        'business.name as name',
        'business.img_profile as img_profile',
        'business.username as username'
    )
    ->where('business.city_id', $city)
    ->inRandomOrder()
    ->limit('10')
    ->get();

foreach($posts as $post){
    $data[$i] = [
        "user" => [
            "username" => $post->username,
            "img_profile" => $post->img_profile
        ],
        "description" => $post->description,
        "img" => $post->img
    ];
    $i++;
}

Answer 1

Key to your problem is that you think you are using Eloquent, but you are not – you are using Query Builder. Eloquent handles the relationships between your models so you never have to think about tables. If you are using join() then you're not using Eloquent.

From what I can see, you're starting with a City, selecting Business related to this city, and then selecting 10 random Post from the Business? Things are a bit unclear since you seem to be using non-conventional table and column names, but hopefully this will give you an idea where to start.

Step one is setting up a relationship; in addition to typical "City has many Business" and "Business has many Post" you'll want to set up a direct relationship between City and Post like this:

class City extends Model
{
    public function posts()
    {
        return $this->hasManyThrough(Post::class, Business::class);
    }
}

Once that relationship is in place, you should be able to get what you're looking for with something like this:

$city = City::find($city_id);
$data  = $city
    ->posts()
    ->inRandomOrder()
    ->limit(10)
    ->with("business:id,name,img_profile,username")
    ->get();