CODEWITHSUNDEEP
phpmysqllaravelquery-builder
Vollmilchbb
Vollmilchbb

Reputation: 481

Laravel Query Builder

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)

Upvotes: 0

Views: 159

Answers (1)

Antonio Madonna
Antonio Madonna

Reputation: 919

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

Upvotes: 0

Related Questions