CODEWITHSUNDEEP
mysqlsqllaravel
mastercheef85
mastercheef85

Reputation: 2269

Laravel SQL Query Builder

I have 4 tables, 3 data and 2 pivot table:

projects
-----------
id  |  name 

tasks
------------
id  |  name  |  project_id

users
------------
id  |  name

project_user
------------
project_id  |  user_id

task_user
------------
task_id  |  user_id

Now i want to get is a list of all projects which either are assigned directly to a specific user or a task is assigned to this user.

I already tried a lot of things but somehow, it not will really work and i also have no clue how could that with Laravel Query Builder.

Here is my last SQL Query:

$projects = \DB::select( \DB::raw("SELECT * FROM projects, project_user 
WHERE EXISTS (
    SELECT 1 FROM tasks, task_user 
    WHERE tasks.status = 0 
    AND tasks.id = task_user.task_id 
    AND task_user.user_id = '".$userId."' ) 
AND projects.status = 0 
AND projects.id = project_user.project_id 
AND project_user.user_id = '".$userId."'") );

How do i need make the query to achieve it?

Is it possible to make it with Laravel Query Builder?

Thanks for every help!

Upvotes: 1

Views: 193

Answers (1)

Matt
Matt

Reputation: 15061

Use a UNION ALL to get both sets of data without needing to use an OR.

$projects = \DB::select( \DB::raw("
SELECT p.id, p.name 
FROM projects p
INNER JOIN project_user pu ON p.user_id = pu.user_id
UNION ALL
SELECT p.id, p.name 
FROM projects p
INNER JOIN tasks t ON p.id = t.project_id
INNER JOIN task_user tu ON t.id = tu.task_id
") );

Upvotes: 1

Related Questions