Search code examples
sqllaravelraw

Laravel, how I can simply execute complex query

I need count sent emails in a month, and the last sent to everyone. I imagine something like this:

$mes=8; // or any month
$search= [100,101,102,103]; // dni
$sql = "
SELECT A.dni, $mes AS mes, COUNT(*) AS total, B.lastet_date 
FROM emails AS A
LEFT JOIN (
          SELECT dni, MAX(date_sended) AS lastet_date
          FROM emails 
          GROUP BY dni
          ) AS B
          ON B.dni = A.dni 
WHERE MONTH(date_sended) = $mes 
AND A.dni IN ('".implode("', '",$search)."') 
GROUP BY A.dni";

How can I execute it, simply, using raw method or similar?

Solution

  • You can use the DB::select helper:

    use Illuminate\Support\Facades\DB;
...
$results = DB::select($sql)

    This will return a collection where each element is a row of the query result.