Laravel SQL Query Builder

Question

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!

Matt · Accepted Answer · 2016-05-20 08:40:03Z

1

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
") );

answered May 20, 2016 at 8:40

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mastercheef85 Over a year ago

Thanks but it not works, i have now a lot of results. But to be honest, i made a mistake on my description. of course i don't have a user_id on project table when i have a pivot table. I changed your sql to following: $sqlQuery = "SELECT projects.id, projects.name FROM projects INNER JOIN project_user pu ON projects.id = pu.project_id UNION ALL SELECT projects.id, projects.name FROM projects INNER JOIN tasks t ON projects.id = t.project_id INNER JOIN task_user tu ON t.id = tu.task_id"; Any suggest?

Collectives™ on Stack Overflow

Laravel SQL Query Builder

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related