Laravel : Having lot of problems converting SQL query to query builder

Question

i'am having a lot of issue converting this query from MYSQL to laravel query builder... Can somebody help me figure it out ? I'am trying to parse all the projects for the actual user (i will replace the 1 value with actual user later), id's are stored on a different base who have id for projets, id for users, and id for companies.

-- SQL

SELECT *
FROM projets, companies, users, userscompaniesprojets 

WHERE 1 = userscompaniesprojets.id_user 

AND companies.id = userscompaniesprojets.id_companies

AND userscompaniesprojets.id_projet = projets.id;

-- Laravel

$companies = DB::table('projets', 'userscompaniesprojets', 'companies')
            ->where('userscompaniesprojets.id_user', '=', '1')
            ->where('companies.id', '=', 'userscompaniesprojets.id_companies')
            ->where('userscompaniesprojets.id_projet', '=', 'projets.id')
            ->get();

And what are you getting when you run this query?

jedrzej.kurylo
– jedrzej.kurylo

2016-01-13 10:25:00 +00:00
Commented Jan 13, 2016 at 10:25 — jedrzej.kurylo
– jedrzej.kurylo, Commented Jan 13, 2016 at 10:25

Mark Davidson · Accepted Answer · 2016-01-13 10:37:16Z

If you look at the output of your query by using the toSql method

DB::table('projets', 'userscompaniesprojets', 'companies')->
where('userscompaniesprojets.id_user', '=', '1')->
where('companies.id', '=', 'userscompaniesprojets.id_companies')->
where('userscompaniesprojets.id_projet', '=', 'projets.id')->
toSql();

you'll see the resulted SQL is

select * from "projets" 
where "userscompaniesprojets"."id_user" = ? and 
"companies"."id" = ? and "userscompaniesprojets"."id_projet" = ?

this is due to the table method only taking a single table see the API docs

You need to make use of joins when constructing this query see the docs once again.

I think something like this will accomplish what you want

DB::table('projets')->
join('usercompaniesprojects', 'userscompaniesprojets.id_user', '=', '1')->
join('companies', 'companies.id', '=', 'userscompaniesprojets.id_companies')->
where('userscompaniesprojets.id_projet', '=', 'projets.id');

which results in

select * from "projets" 
inner join "usercompaniesprojects" on "userscompaniesprojets"."id_user" = "1" 
inner join "companies" on "companies"."id" = "userscompaniesprojets"."id_companies" 
where "userscompaniesprojets"."id_projet" = ?

For more information on Debugging Queries in Laravel check out this article.

Collectives™ on Stack Overflow

Laravel : Having lot of problems converting SQL query to query builder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related