Datatable Search return Sql Excelption

[karimos97]

when the table loads for the first time it loads normally but if I type something in the search box I get an SQL Exception this is my code:

Code snippet of problem

public function getProducts(Request $request)
{
        $data = Product::
                select(['products.id',
                        'products.name',
                        'products.price',
                        'products.cost',
                        'products.is_group as pack',
                        'products.created_at',
                         DB::raw("(select path from product_images where product_id =products.id and CASE WHEN path IS NULL THEN  'productImages/no-image.png' else path  END) as path")
                ]);
        if ($request->wirehouse)
        { 
            $wirehouse = $request->wirehouse;
            $data->selectRaw('(select if(sum(stocks.qte) > 0 , sum(stocks.qte) , 0) from stocks
                                       where stocks.product_id = products.id and
                                       stocks.wirehouse_id = ' . $wirehouse . ')
                                       - (select if(sum(stocks.qte) > 0 , sum(stocks.qte) , 0)
                                       from stocks where stocks.product_id = products.id and stocks.stock_id
                                       in (select stocks.id from stocks where stocks.product_id = products.id
                                       and stocks.wirehouse_id = ' . $wirehouse . ') ) as total_stock ,
                                       (select if(sum(items.quantity) > 0 , sum(items.quantity) , 0) from orders,
                                       items where orders.id = items.order_id and items.product_id = products.id
                                       and (orders.status in (4,5,6,9,11) or (orders.status = 8 and orders.recover = 0))
                                       and items.wirehouse_id = ' . $wirehouse . ') as out_stock');
            $data->havingRaw('total_stock - out_stock != 0');
        }
        else
        {
            $data->selectRaw('(select if(sum(stocks.qte) > 0 , sum(stocks.qte) , 0) from stocks where stocks.product_id = 
                                                   products.id and stocks.stock_id = 0  and stocks.confirm = 1) as total_stock ');
            
            $data->selectRaw('(select if(sum(items.quantity) > 0 , sum(items.quantity) , 0) from orders,
                                       items where orders.id = items.order_id and items.product_id = products.id
                                       and (orders.status in (4,5,6,9,11) or (orders.status = 8 and 
                                       orders.recover = 0))) as out_stock');    

            $data->selectRaw('(select ifnull( sum(items.quantity*groups.qte) , 0) from orders
                                       JOIN items on items.order_id=orders.id join `groups` on
                                       groups.main = items.product_id where orders.id = items.order_id 
                                       and groups.product_id=products.id and (orders.status in (4,5,6,9,11) or
                                       (orders.status = 8 and orders.recover = 0))) as grp ');
        }
        $data->orderBy(DB::raw('total_stock - out_stock + grp') , "Desc");

        return DataTables::of($data)->make(true);
}

this is the Error I get when I type anything in the search box:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.product_id = products.id and stocks.`stock_id = 0 and' at line 12

System details

• Operating System Centos
• PHP Version 7.4.16
• Laravel Version 8
• Laravel-Datatables Version 9

[yajra]

As the error suggest, you have to fix your SQL. Try executing the generated sql first if works directly.

Also, your query is quite complex, I suggest you use a SQL View if possible.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.