mysql - 需要帮助将 sql 查询转换为 laravel 查询

Question

我需要在 laravel 查询中使用以下 sql 查询，或者我怎样才能按原样运行 laravel。谢谢

SELECT  t.*
FROM    (SELECT client_id,product_name, max(real_date) AS max_date
        FROM api_hilbertis.transactions
        WHERE active = 1 AND real_date <= '2019-03-01'
        GROUP BY client_id, product_name) AS m

INNER JOIN api_hilbertis.transactions AS t
        ON t.client_id = m.client_id
        AND t.product_name = m.product_name
        AND t.real_date = m.max_date
        AND active = 1
ORDER BY real_date DESC;

Answer 1

您可以使用 laravel 原始查询。示例 HERE

所以对于你的情况，我们可以这样写:

$results = DB::select( DB::raw("
SELECT  t.*
FROM    (SELECT client_id,product_name, max(real_date) AS max_date
        FROM api_hilbertis.transactions
        WHERE active = 1 AND real_date <= '2019-03-01'
        GROUP BY client_id, product_name) AS m

INNER JOIN api_hilbertis.transactions AS t
        ON t.client_id = m.client_id
        AND t.product_name = m.product_name
        AND t.real_date = m.max_date
        AND active = 1
ORDER BY real_date DESC"
 ));

编辑:带有变量的示例:

$someVariable = $request->input('some_variable');

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), 
['somevariable' => $someVariable,]
);