CODEWITHSUNDEEP
phplaravellaravel-5eloquentlaravel-5.4
SaidbakR
SaidbakR

Reputation: 13534

Laravel eloquent where pivot null or less than

I have timestamp field, named closed, in pivot table for Many to Many relationship. I want to get items where closed is null or is less than a specific time. I tried the following:

// In model
public function jobEquipments($job, $one = false)
    {
        $nowDt = Carbon::createFromFormat('Y-m-d H:i:s', date('Y-m-d H:i:s'));
        if (!$one){
        return $this->belongsToMany(Equipment::class, 'cavity_actions', 'cavity_id', 'equipment_id')
                ->wherePivot('job_id', $job)
                ->withPivot('created_at','aid')
                ->wherePivot('closed',null)
                ->orWherePivot('closed','<',date('Y-m-d H:i:s'))
                ->orderBy('pivot_created_at', 'desc');
            }
....

I also tried to use $nowDt instead of date('Y-m-d H:i:s') in orWherePivot However, there is no change to the query result. i.e It looks like the same values without ->orWherePivot('closed','<',date('Y-m-d H:i:s')) clause. In my database, I'm pretty sure that there is enough records with closed datetime value less than Now.

Upvotes: 1

Views: 5947

Answers (2)

MingalevME
MingalevME

Reputation: 1975

Works at least with PostgreSQL (Illuminate\Database\Query\Grammars\PostgresGrammar):

# wherePivotIsNull
->wherePivot(SomeModelName::DELETED_AT, 'is not distinct from', DB::raw('null')

# wherePivotIsNotNull
->wherePivot(SomeModelName::DELETED_AT, 'is distinct from', DB::raw('null')

Upvotes: 0

Laerte
Laerte

Reputation: 7073

Try to isolate the or statements. Try this:

$this->belongsToMany(Equipment::class, 'cavity_actions', 'cavity_id', 'equipment_id')
                ->wherePivot('job_id', $job)
                ->withPivot('created_at','aid')
                ->where(function ($q) {
                    $q->where('cavity_actions.closed',null)
                      ->orWhere('cavity_actions.closed','<',date('Y-m-d H:i:s'));
                })
                ->orderBy('pivot_created_at', 'desc');

Upvotes: 3

Related Questions