How to select record from table whose id is not there in another table using Laravel model?

Question

List item

I need to select data from a table whose id does not exits in another table or if exists then check other conditions. i can achieve this by

Task::join('events','events.task_id' ,'=','tasks.id')
        ->where('events.event_type','=','Task')
        ->where('events.task_stage','!=','assigned')->select('tasks.*');

but i don't think this is the right way.

For Example:

+-------------------------+   +-------------------------+
| tasks                   |   | Events                  |
+----------+--------------+   +-------------------------+
| id       |  name        |   | task_id  |  task_stage  |
| 1        |  Task1       |   | 1        |  'assigned'   |
| 2        |  Task2       |   | 2        |  'created'   |
+----------+--------------+    +----------+--------------+

Result Should be:

Task with id 2 which is only created not assigned. I am new to queries. Thanks in advance...

Have you created your table models? – Muhammad Jun 17 '19 at 07:04 — Muhammad, Jun 17 '19 at 07:04

score 1 · Accepted Answer · answered Jun 17 '19 at 07:11

1

Pass a Closure as the second argument into the join method. The Closure will receive a JoinClause object which allows you to specify constraints on the join clause:

Task::join('events', function ($join) {
    $join->on('events.task_id', '=', 'tasks.id')
        ->where('events.event_type', '=', 'Task')
        ->where('events.task_stage', '!=', 'assigned');
})->select('tasks.*')->get();

Documentation

answered Jun 17 '19 at 07:11

linktoahref

7,812
3
29
51

hello @linktoahref there is one more problem now – Neha Jun 18 '19 at 05:26
The Output record gives the id of events table instead of tasks table.. Why? – Neha Jun 18 '19 at 05:28
https://stackoverflow.com/questions/14727038/laravel-eloquent-how-to-get-only-certain-columns-from-joined-tables/14728992 – linktoahref Jun 18 '19 at 06:54

How to select record from table whose id is not there in another table using Laravel model?

1 Answers1