CODEWITHSUNDEEP
mysqllaraveleloquent
mvn.2047
mvn.2047

Reputation: 362

How to make DB request in Laravel 6 for a difficult Request?

My request is: 

SELECT DATE(`main_date`) `date`, SUM(`total`) `sum_value` FROM 
SELECT db1.main_date, db2.total FROM database_1 db1 INNER JOIN database_2 db2 ON db1.id = db2.main_id 
WHERE (user_id LIKE 111 AND DATE(`main_date`) LIKE '2020-01-01')) AS totals 
GROUP BY DATE(`main_date`);

It works perfect in SQL, however in Laravel I could make this part: 

$db->select(DB::raw("DATE(`main_date`) `date`, SUM(`total`) `sum_value`")
->groupBy(DB::raw("DATE(`main_date`)")
->get();

But where to place main the most important part: 

FROM 
    SELECT db1.main_date, db2.total FROM database_1 db1 INNER JOIN database_2 db2 ON db1.id = db2.main_id 
    WHERE (user_id LIKE 111 AND DATE(`main_date`) LIKE '2020-01-01')) AS totals

I really don't have any idea about it. :( Thank you for your support!

Upvotes: 1

Views: 56

Answers (1)

Герман Швецов
Герман Швецов

Reputation: 99

Try to use DB facade with your query, like this: 

DB::select('SELECT DATE(`main_date`) `date`, SUM(`total`) `sum_value` FROM 
SELECT db1.main_date, db2.total FROM database_1 db1 INNER JOIN database_2 db2 ON db1.id = db2.main_id 
WHERE (user_id LIKE 111 AND DATE(`main_date`) LIKE '2020-01-01')) AS totals 
GROUP BY DATE(`main_date`)')

Upvotes: 1

Related Questions