Subquery on relationship

Question

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?

boolfalse · Answer 1 · 2021-03-27T00:25:23.980

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

IGP · Answer 2 · 2021-03-27T00:21:54.863

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.

Subquery on relationship

2 Answers2