Datatables is broken with complex query after updating because of PR 1730

[royduin]

Just updated Datatables because I updated Laravel and now it's broken:

Exception Message:

SQLSTATE[HY093]: Invalid parameter number (SQL: select count(*) as aggregate from (select '1' as `row_count` from (select `customers`.*, (select COUNT(`id`) from `orders` where `orders`.`customer_id` = `customers`.`id` and `ordered_at` between Alkmaar and 4) as `order_count_2017` from `customers`) as t where `city` = ? and `order_count_2017` = ?) count_row_table)

After some digging this is causing it: #1730. It removes my select bindings. As you can see in the error my "where bindings" are used for the "select bindings".

The query I'm using:

$subQuery = DB::table('orders')
    ->selectRaw('COUNT(`id`)')
    ->whereColumn('orders.customer_id', 'customers.id')
    ->whereBetween('ordered_at', ['2017-01-01 00:00:00', '2017-12-31 23:59:59']);

$query = DB::table('customers')
    ->select('customers.*')
    ->selectSub($subQuery, 'order_count_2017');

$wrapperQuery = DB::table(DB::raw("({$query->toSql()}) as t"))
    ->mergeBindings($query)
    ->where('city', 'Alkmaar')
    ->where('order_count_2017', 4);

All of this is needed so I can do a "where" on my subselect. This is needed because "having" isn't supported. Can we revert #1730 and find another solution for that? Just removing all select bindings isn't the solution @fschalkwijk and @yajra...

[yajra]

Thanks for reporting, will try to dig this further. Reverting the PR might break the other one that was fixed #1600.

[yajra]

BTW, please do not hesitate to submit a PR if you can. Thanks!

[royduin]

Do you think we can somehow detect if it should be removed or give it a parameter?

[royduin]

Tried to auto detect it, for now just a flag which can be set.