CODEWITHSUNDEEP
sqllaravel-5eloquent
Alexis Romot
Alexis Romot

Reputation: 534

Laravel/Eloquent: querying a belongsTo / hasMany relation

I have 2 tables : tasks and executions. A task "hasMany" executions (and an execution "belongsTo" a task).

Fields in table tasks: id (primary key), name, data, created_at, ...
Fields in table executions: id (primary key), process_name, created_at, task_id.

I want to get the oldest task (based on tasks.created_at) which,
- has never been been executed (no record in table executions with the task id as task_id),
OR
- has been executed by any other process but "MyProcess" => executions.process_name <> 'MyProcess'

I read the Eloquent doc and find the "Querying Relationship Existence" but I can't find how to query non-existence.

How can I do (I hope I was clear enough)? Thank you very much in advance, I'm stuck!

Upvotes: 3

Views: 1392

Answers (2)

sebdesign
sebdesign

Reputation: 838

First you should set up your relationships:

class Task extends Model {
    public function executions() {
        return $this->hasMany(Execution::class);
    }
}

class Execution extends Model {
    public function task() {
        return $this->belongsTo(Task::class);
    }
}

Then your query should be:

$task = Task::doesntHave('executions')
            ->orWhereHas('executions', function($query) use ($name) {
                return $query->where('process_name', '<>', $name);
            })
            ->oldest()
            ->first();

Of course you could wrap those queries in a query scope.

Upvotes: 3

Vered Rosen
Vered Rosen

Reputation: 381

Try the following:

SELECT t.* FROM tasks t 
LEFT JOIN executions e 
ON e.task_id = t.id AND e.process_name = 'MyProcess'
WHERE e.task_id IS NULL
ORDER BY t.create_at LIMIT 1

Upvotes: 0

Related Questions