CODEWITHSUNDEEP
laraveleloquent
ffuentes
ffuentes

Reputation: 1182

Query to get the previous element related to the id in Laravel Eloquent

I have two tables. One is Device and the other is POS in a many to many relationship. What I need is to get all the devices from the POS id I'm seeking but get the previous device, not the latest one. I wanna use it as a function on my model to have easy access to that element on Device.

Device
--------
- id
- other elements 
- pos_id
- function to get the previous device for the above pos_id

POS
--------
- id
- other stuff

Devices_pos
----------
- device_id
- pos_id

I know I can access $pos->devices and get all the elements.

I've tried:

public function scopePrev_dev_pos()
    {
        if ($this->pos_id > 0){
            $pos = Pos::find($this->pos_id);
            return $pos->devices->where('device_id', '<', $this->id)->max('device_id');
        } else {
            return null;
        }

    }

All it gives me is an Eloquent Builder that apparently doesn't have anything inside.

The query is supposed to be something like this: 

SELECT MAX(device_id) FROM devices_pos
WHERE pos_id = 7
AND device_id < 7035;

... but using Eloquent. ids are only an example.

EDIT: Ended up using an accessor:

public function getPreviousDeviceAttribute()
    {
        if ($this->pos_id > 0){
            $pos = Pos::find($this->pos_id);
            $prev = DB::table('devices_pos')->where('device_id', '<', $this->id)
                    ->where('pos_id', $this->pos_id)
                    ->max('device_id');
            $device = Device::find($prev);
            return $device;
        } else {
            return null;
        }

    }

I leave the question open because I'm using the facade and not Eloquent but it's a good workaround. A local scope was not useful for my issue (if you need to test for null it won't work).

Upvotes: 1

Views: 353

Answers (1)

Davit Zeynalyan
Davit Zeynalyan

Reputation: 8618

Try this it must be work

$pos->devices()->newPivot()->where([ ['device_id', '<', $this->id], ['pos_id','=', $this->pos_id] ])->max('device_id');

Or you can use

DevicePos::where([
    ['device_id', '<', $this->id],
    ['pos_id','=', $this->pos_id]
])->max('device_id');

see related https://laravel.com/docs/5.5/queries#aggregates

Example with DB facade

DB::table('devices_pos')->where([
        ['device_id', '<', $this->id],
        ['pos_id','=', $this->pos_id]
    ])->max('device_id');

Upvotes: 1

Related Questions