Invalid parameter number error using DataTables with Laravel

Question

When DataTables tries to get data, it always gets tripped up on this Eloquent query:

$items = Item::select([
        DB::raw("images.url AS image"),
        'items.id',
        'items.sku',
        'items.quantity',
        DB::raw("IF(items.enabled, 'Yes', 'No') AS enabled")
    ])
        ->leftJoin('images', function ($j) {
            $j->on('images.imageable_id', '=', 'items.id')
                ->where('images.imageable_type', '=', 'Item');
        })
        ->leftJoin('order_items', 'items.id', '=', 'order_items.item_id')
        ->leftJoin('orders', 'orders.id', '=', 'order_items.order_id')
        ->where('items.store_id', 1)
        ->whereNull('items.deleted_at')
        ->whereIn('items.status', ['active', 'submitted'])
        ->groupBy('items.id');

The query works just fine as is and returns the desired results. However, DataTables tries to transform it into the following which produces the error:

select count(*) as aggregate from (select '1' as row from `items` left join `images` on `images`.`imageable_id` = `items`.`id` and `images`.`imageable_type` = 1 left join `order_items` on `items`.`id` = `order_items`.`item_id` left join `orders` on `orders`.`id` = `order_items`.`order_id` where `items`.`store_id` = 1 and `items`.`deleted_at` is null group by `items`.`id`) AS count_row_table

This produces this error specifically:

SQLSTATE[HY093]: Invalid parameter number
/home/vagrant/Projects/test.dev/vendor/laravel/framework/src/Illuminate/Database/Connection.php#301

When I execute that query directly on the MySQL database, it has no problem. This seems to be happening within Laravel only.

If I remove the ->leftJoin('images', function ($j) {...} part of the query then there is no error, but I need that join for the image.

How to get around this error?

Full error output returned to DataTables over AJAX:

{  
   "error":{  
      "type":"Illuminate\\Database\\QueryException",
      "message":"SQLSTATE[HY093]: Invalid parameter number (SQL: select count(*) as aggregate from (select '1' as row from `items` left join `images` on `images`.`imageable_id` = `items`.`id` and `images`.`imageable_type` = 1 left join `order_items` on `items`.`id` = `order_items`.`item_id` left join `orders` on `orders`.`id` = `order_items`.`order_id` where `items`.`store_id` = active and `items`.`deleted_at` is null and `items`.`status` in (submitted, ?) group by `items`.`id`) AS count_row_table)",
      "file":"\/home\/vagrant\/Projects\/test.dev\/vendor\/laravel\/framework\/src\/Illuminate\/Database\/Connection.php",
      "line":625
   }
}

Can you show the exact raw query being executed (without replacing any placeholders), and the bindings passed to the query? If you have it available, the value of the aQueries key in the result from the Datatables::of($items)->make(); statement would be great. Otherwise, I think you should be able to get this from the query log. — patricus
– patricus, Commented Jan 23, 2015 at 7:05
It never gets to make(). Bizarrely, when I look at the AJAX response returned to DataTables, I see items.status in (submitted, ?) which makes no sense, as the placeholder in that statement is a string literal active from the Eloquent whereIn('items.status', ['active', 'submitted']) portion of the query. Also, items.store_id = active is actually supposed to be 1 instead of active. — eComEvo
– eComEvo, Commented Jan 23, 2015 at 23:51
What do you get from a dd(var_export($items->toSql(), true).PHP_EOL.var_export($items->getBindings(), true)); right after your $items assignment? What version of the DataTables package are you using? Having you tried updating to the latest tagged release (I would not suggest using the master branch)? — patricus
– patricus, Commented Jan 24, 2015 at 0:16

kevbaldwyn · Accepted Answer · 2015-03-05 15:44:32Z

1

I have had a similar issue today, not with DataTables but with a complex query being built using the standalone query builder. The issue was similar to yours with the left join, passing a closure to build a more complex join condition. I was using a where condition after the "on".

The solution was to chain "on" calls:

->leftJoin('images', function ($j) {
        $j->on('images.imageable_id', '=', 'items.id')
            // note the "on" rather than the "where" and the use of a raw statement
            ->on('images.imageable_type', '=', DB::raw('Item'));
    })

answered Mar 5, 2015 at 15:44

kevbaldwyn

464 bronze badges

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

Comments

omg · Accepted Answer · 2015-02-05 08:32:46Z

0

I had this exact issue. The work around is not perfect since it will basically grab all the data twice, but it's the only way I could get it to work.

You have to do a ->get(); before sending it to ->make();. I honestly hope someone finds the right solution but for now:

Temp solution:

$data = DB::table('some_table');

$data->leftJoin('some_other_table', function($join)
{
    $join->on('some_table.id', '=', 'some_other_table.id')
    ->where('some_table.something', '=', 'some_value');
});

$data->get();

return Datatables::of($data)->make();

This is with using the datatables package for Laravel: https://github.com/bllim/laravel4-datatables-package

answered Feb 5, 2015 at 8:32

omg

161 bronze badge

Collectives™ on Stack Overflow

Invalid parameter number error using DataTables with Laravel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related