php - Laravel 查询生成器

Question

我在使用 Laravel 查询生成器时遇到了一些问题。我想转换这个 SQL 查询:

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

我尝试执行以下操作:

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

但它仍然告诉我某处有语法错误。任何帮助将不胜感激。

错误 MSG := SQLSTATE[42000]: 语法错误或访问冲突: 1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在“SELECT * FROM Price ASfilteredPrices on articles.ItemNo = filteredPrices”附近使用的正确语法>.'在第 1 行(SQL:选择 ItemNo、Description_DEU、Description_ENG、Description_FRA、Description_ESP、Description_ITA、PriceId、数量、PricePerItem、PriceSheet_Id 来自 文章 left join SELECT * FROM 价格 ASfilteredPrices on articles.ItemNo = filteredPrices.ItemNumber 其中PriceSheet_Id = 1)

Answer 1

要构建 SQL 查询，您应该将 where 条件放入原始子查询中，并为子查询指定别名以在 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();