Avoiding n queries on API list call for database-calculated model attributes

Question

I am cleaning up a quite messy php laravel 8 project right now.

Currently my ressource looks like this:

class MyModelSimpleResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'my_calculated_attribute' => MyModel::whereRaw('fk_id = ? AND model_type = "xyz"', [$this->id])->count(),
        ];
    }
}

My problem is that on calling the API endpoint, for every record of MyModel it creates a separate query to calculate my_calculated_attribute.

I know that you can define foreign key constrains in the model like and query like this:

MyModel::with('myFkModel')->get()

This works great for foreign keys. But how can I avoid n queries when I need this my_calculated_attribute.

Thanks a lot!

PS: I know that raw queries are a bad idea and I know that the resource is supposed to transform data and not query it.

Answer 1

Ok, I figured it out. I don't only can define foreign keys in my model! I just defined a new relationship:

public function myFkModels(): HasMany
{
    return $this->hasMany(MyFkModel::class);
}

public function myFkWithCondition(): HasMany
{
    return $this->myFkModels()->where('model_type ', 'xyz');
}

This I can put in my with statement like this:

MyModel::with('myFkModels', 'myFkWithCondition')->get()