Tengo una consulta de base de datos mysql y quiero implementar el generador de consultas laravel
with recursive dates as ( select date('2021-01-01') date union all select dates.date + interval 1 day from dates where dates.date < '2021-01-31' ) select dates.date, count(payments.id) from dates left join payments on date(payments.created)=dates.date group by 1;Por lo que puedo decir, la única forma de agregar una cláusula WITH a una consulta es usar sql sin procesar.
$rawSql = <<<SQL with recursive dates as ( select date('2021-01-01') date union all select dates.date + interval 1 day from dates where dates.date < '2021-01-31' ) select dates.date, count(payments.id) from dates left join payments on date(payments.created)=dates.date group by 1; SQL; $results = DB::select($rawSql);Si necesita cambiar algunos valores, puede hacerlo con enlaces.
$rawSql = <<<SQL with recursive dates as ( select date('?') date union all select dates.date + interval 1 day from dates where dates.date < '?' ) select dates.date, count(payments.id) from dates left join payments on date(payments.created)=dates.date group by 1; SQL; $results = DB::select($rawSql, ['2021-01-01', '2021-01-31']); Usando staudenmeir/laravel-cte , puede escribir la cláusula WITH de esta manera:
withRecursiveExpression('dates', function ($rec) { $rec->selectRaw("date('2021-01-01') date") ->unionAll(function ($uni) { $uni->selectRaw('dates.date + interval 1 day') ->from('dates') ->whereRaw("dates.date < '2021-01-31'"); }); })La parte recursiva de la consulta seguía arrojando un error si usaba enlaces, por lo que, lamentablemente, las fechas pasan sin formato.
En teoría, esta sintaxis debería funcionar para la consulta completa.
$res = DB::query() ->withRecursiveExpression('dates', function ($rec) { $rec->selectRaw("date('2021-01-01') date") ->unionAll(function ($uni) { $uni->selectRaw('dates.date + interval 1 day') ->from('dates') ->whereRaw("dates.date < '2021-01-31'"); }); }) ->select('dates.date', DB::raw('count(payments.id)')) ->from('dates') ->leftJoin('payments', DB::raw('date(payments.created)'), 'dates.date') ->groupByRaw('1') ->get();El SQL generado es ligeramente diferente.
with recursive `dates` as ( (select date('2021-01-01') date) union all (select dates.date + interval 1 day from `dates` where dates.date < '2021-01-31') ) select `dates`.`date`, count(payments.id) from `dates` left join `payments` on date(payments.created) = `dates`.`date` group by 1