How to implement recursive query in laravel builder

Question

I have mysql database query and i want to implementing laravel query builder

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;

IGP · Accepted Answer · 2022-01-18 01:22:41Z

6

From what I can tell, the only way to add a WITH clause to a query is to use raw sql.

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

If you need to change some values, you can do so with bindings.

$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']);

EDIT

Using staudenmeir/laravel-cte, you can write the WITH Clause like this:

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'");
        });
})

The recursive part of the query kept throwing an error if I used bindings, so unfortunately, the dates are passes in raw.

In theory, this syntax should work for the complete query.

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

The generated SQL is slightly different.

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

edited Jan 18, 2022 at 1:22

answered Jan 17, 2022 at 19:12

IGP

16.4k5 gold badges34 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ceejayoz Over a year ago

OP's just using the WITH clause to generate a list of dates; that could easily be generated on the Laravel end and provided with a whereIn clause. A direct translation isn't necessary.

IGP Over a year ago

In this case, yes. But that might not be a great idea if the date list grows too big. whereIn adds a binding per element.

ysth Over a year ago

I'd like to see if/how the laravel cte extension github.com/staudenmeir/laravel-cte could be used for this

IGP Over a year ago

@ysth there you go.

mohammad13 Over a year ago

Thanks. I commonly used staudenmeir/laravel-cte to build the query. but i just want to do with laravel query builder purly.

Collectives™ on Stack Overflow

How to implement recursive query in laravel builder

1 Answer 1

EDIT

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related