CODEWITHSUNDEEP
laraveleloquent
Christoffer
Christoffer

Reputation: 7777

Subquery on relationship

I'm trying to do some aggregation with relationships to build an api response. Forms and Categories have many-to-many in both directions.

This work:

 $forms = $organisation->forms()->with('categories')->get()->toArray();

And it gives me a nice array with all the related category models. But I would like to make a subquery to make the categories be just a flat array with the id's.

So I'm trying to figure out how to do this:

 $forms = $organisation->forms()->with('categories')->[ pluck('categories.id')->toArray() ]->get()->toArray();

That doesn't work of course. But hopefully it explains what I'm after. So I want to do an eager loading of the categories relationship, but I only want to fetch the id's of the categories - not the whole model.

Is that even doable?

Upvotes: 0

Views: 2259

Answers (2)

boolfalse
boolfalse

Reputation: 2103

Eloquent have something useful for that:

Assume you have "categories.form_id" foreign key field, which references to "forms.id". Without selecting "form_id", you will not get the categories, but the empty array instead.

$forms = $organisation->forms()
    ->with(['categories' => function($query) {
        $query->select(['id', 'form_id']);
    }])
    ->get()
    ->toArray();

Also there's another shortly way to do that (with the same result):

$forms = $organisation->forms()
    ->with('categories:categories.id,form_id')
    ->get()
    ->toArray();

Note: TRY to avoid to use eloquent methods after the "->get()" and try do work with them on extreme cases. Cuz the "get()" method just invokes and retrieve the results from query builder. Usage of any official methods after the "get()" will just increase the retrieving and conversion time, and will speed down your performance.

Official Docs: Eager Loading Specific Columns

Additional Q&As: 1, 2, 3

Upvotes: 1

IGP
IGP

Reputation: 15786

Eloquent needs to select the foreign id in its hasMany relationships. A way you could force the result to look the way you want is by using the setRelation method on every item in the resulting collection.

$forms = $organisation->forms()
    ->with('categories')
    ->get()
    ->each(function ($form) {
        $form->setRelation('categories', $form->categories->pluck('id')->sort()->values());
    })
    ->toArray();

This will turn the categories relationship into a collection of ids, which will then turn into an array of ids when you convert the forms collection to an array.

If you want the categories relationship to be na array before that, you can call toArray() inside the setRelation() method.

$forms = $organisation->forms()
    ->with('categories')
    ->get()
    ->each(function ($form) {
        $form->setRelation('categories', $form->categories->pluck('id')->sort()->values()->toArray());
    });
    // ->toArray();

Another way you could do that would be to read up on API resources.

Upvotes: 1

Related Questions