use where clauses to get data from join query in Laravel

Question

I am creating a system using Laravel and AngularJS where I assign tasks to users. Multiple tasks has multiple users and vice versa. In the Database, I have this tables:

task: id | name

task_users: id | task_id | user_id

users: id | name

In my view, I display a particular task, using id of task table. I display a list of users (called unassigned users) who are not assigned to that particular task. When that user is assigned, it's name gets removed from the list.

To achieve this, I used this query:

    public static function remainingUser($task_id)
    {
    return \DB::table('users')
        ->leftjoin('task_users', 'task_users.user_id', '=', 'users.id')
        ->select('users.id',
            'users.name as name'
        )
        ->where('task_id', '!=', $task_id)
        ->orWhere('task_id', null)
        ->get();
    }

Suppose I have this data

task:

id | name

1 | Task1

2 | Task2

3 | Task3

users:

id | name

1 | User1

2 | User2

3 | User3

4 | User4

5 | User5

6 | User6

7 | User7

8 | User8

9 | User9

10 | User10

task_users:

id | task_id | user_id

1 | 1 | 1

1 | 1 | 2

1 | 1 | 3

1 | 1 | 5

1 | 1 | 6

1 | 1 | 7

1 | 2 | 2

1 | 2 | 4

Now suppose I am displaying task details of task_id = 1 and I want to assign user 4 to this task. So my list of unassigned users should contain all the users who are not assigned this task. My query does not return the required data. I have also tried different conditions in where clause, but I do not get the correct required data. What am I doing wrong?

Check [this](https://stackoverflow.com/questions/43821403/laravel-belongstomany-where-doesnt-have-one-of) question/answer. If you have well set models, tables and relations that would be the answer. — Tpojka, Oct 02 '18 at 17:45

score 0 · Answer 1 · answered Oct 02 '18 at 17:43

The issue exists because when you select from Users, you get all users left Joined with a single instance of tasks. So if User1 has Task1 and Task2, only Task1 will be matched here, because it will match User1 to the 1st row found for him within task_users. In order to list the unasigned users, your query would look similar to this:

public static function remainingUser($task_id)
{
return \DB::table('task_users')
    ->rightJoin('users', 'task_users.user_id', '=', 'users.id')
    ->select(
      \DB::raw("DISTINCT(`users`.`id`)"),
      'users.name as name'
    )
    ->where('task_id', '!=', $task_id)
    ->orWhere('task_id', null)
    ->get();
}

use where clauses to get data from join query in Laravel

1 Answers1