Laravel Query Builder

Question

I have some trouble with Laravel Query Builder. I want to convert this SQL Query:

SELECT ItemNo, Description_DEU AS DescDE, Description_ENG AS DescEN, Description_FRA AS DescFR, Description_ESP AS DescES, Description_ITA AS DescIT, PriceId, Quantity, PricePerItem, PriceSheet_Id AS SheetId 
FROM articles 
LEFT JOIN (SELECT * FROM price WHERE PriceSheet_Id = :$variable) AS filteredPrices 
ON articles.ItemNo = filteredPrices.ItemNumber

I tried doing the following:

$results = DB::table('articles')
    ->select('ItemNo', 'Description_DEU', 'Description_ENG', 'Description_FRA', 'Description_ESP', 'Description_ITA', 'PriceId', 'Quantity', 'PricePerItem', 'PriceSheet_Id')
    ->leftJoin(DB::raw('SELECT * FROM price AS filteredPrices'), function($join) {
            $join->on('articles.ItemNo', '=', 'filteredPrices.ItemNumber');
    })
    ->where('PriceSheet_Id', '=', $var)
    ->get();

But It still tells me that I have a syntax error somewhere. Any help would be appreciated.

Error MSG := SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM price AS filteredPrices on articles.ItemNo = filteredPrices.' at line 1 (SQL: select ItemNo, Description_DEU, Description_ENG, Description_FRA, Description_ESP, Description_ITA, PriceId, Quantity, PricePerItem, PriceSheet_Id from articles left join SELECT * FROM price AS filteredPrices on articles.ItemNo = filteredPrices.ItemNumber where PriceSheet_Id = 1)

Maybe you should try put ->toSql() at the end, and dd() that. That'll give you the raw SQL query and you can see what it's constructing. — Pavlin
– Pavlin, Commented Oct 29, 2015 at 9:59

Antonio Madonna · Accepted Answer · 2015-10-29 10:17:44Z

0

To build the SQL query provided you should put the where condition in the raw subquery, and alias the subquery to reference it in the ON:

$results = DB::table('articles')
    ->select('ItemNo', 'Description_DEU', 'Description_ENG', 'Description_FRA', 'Description_ESP', 'Description_ITA', 'PriceId', 'Quantity', 'PricePerItem', 'PriceSheet_Id')
    ->leftJoin(DB::raw('(SELECT * FROM price AS filteredPrices WHERE PriceSheet_Id = ' . $var .') as filteredPrices'), function($join) 
    {
        $join->on('articles.ItemNo', '=', 'filteredPrices.ItemNumber');
    })
    ->get();

answered Oct 29, 2015 at 10:17

Antonio Madonna

9191 gold badge9 silver badges18 bronze badges

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

3 Comments

Antonio Madonna Over a year ago

however, the syntax error was due to the missing parenthesis in the subquery

Vollmilchbb Over a year ago

which one do you mean?

Antonio Madonna Over a year ago

The SQL syntax error was caused by the missing parenthesis around SELECT * FROM price AS filteredPrices WHERE PriceSheet_Id = ' . $var .'

Collectives™ on Stack Overflow

Laravel Query Builder

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related