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++;
}

@miken32 $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(); — Marvin Quiñónez
– Marvin Quiñónez, Commented Feb 17, 2021 at 1:17
Well the first red flag I see is no relationships set up between models. You shouldn't have to manually join in your business table, rather something like Post::with("business", "city"). Eloquent can do this, but you're not going to be able to do what you want with just query builder. — miken32
– miken32, Commented Feb 17, 2021 at 1:23

miken32 · Accepted Answer · 2021-02-17 15:30:13Z

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();

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related