CODEWITHSUNDEEP
phplaravelsearcheloquentattributes
BrianA
BrianA

Reputation: 11

Laravel Where Clause To Search In Columns Which Are Not In The Database

I have a table organisations with a number of columns, namely id, name, created_at and updated_at.

In my Organisation model, I added a custom attribute by adding the following code in the model:

// Add custom attributes
    protected $appends = [
        'device_count',
    ];

and:

public function getDeviceCountAttribute()
    {
        // Count organisation's devices
        $device_count = Device::where('organisation_id', '=', $this->id)->count();
        return $device_count;
    }

In my controller, I am trying to search (using the where clause) by the device_count attribute, but I'm getting an error since this is not a real column in my database table.

$organisations = Organisation::query();
$organisations = $organisations->where('device_count', '=', 0);
$organisations = $organisations->get();

[2020-10-14 12:29:27] local.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'device_count' in 'field list'...

Is there an alternative to the where clause in order to search by device_count please?

Upvotes: 1

Views: 519

Answers (2)

Dilip Hirapara
Dilip Hirapara

Reputation: 15296

As per your requirement you want to get those Organisations which doesnot have any devices

For that, you need to use doesntHave method.

Make a relationship in the Organisation model.

public function devices() {
    return $this->hasMany('App\Models\Device','organisation_id','id');
}

Now call the mothed in controller/

$organisations = Organisation::doesntHave('devices')->get();

it will return you those organisations which devices are not exists.

Upvotes: 1

Qirel
Qirel

Reputation: 26450

You can't query against attributes like that, they only exist on the model and do not exist in the database. However, you don't need to create a getter for that.

Laravel has a withCount() method you can use instead of that attribute - which is the correct approach of obtaining relational counts.

So a query with that attribute would look like

$organisations = Organisation::withCount('devices')->get();

The resulting models will now have a device_count attribute on them, that you can use in your views.

Then in your query, to filter those records that has at least a certain relational count, you can do

$organisations = Organisation::has('devices', '=', 0)->get();

Since you're looking for those that do not have any relation, you can replace it with doesntHave(). The above snippet is included to show how you can query against a specific set of relational counts.

To query only those that don't have any related devices, simply do

$organisations = Organisation::doesntHave('devices')->get();

This assumes that you have defined a devices relation on your Organisation class.

public function devices() {
    return $this->hasMany(App\Models\Device::class); // Or App\Device::class if your models are not in the Models namespace/directory
}

The approaches above are the Laravel-way of doing it - and you should therefor remove your getDeviceCountAttribute() method and the corresponding device_count from $appends.

Upvotes: 3

Related Questions