CODEWITHSUNDEEP
phpmysqllaravel
fobus
fobus

Reputation: 2058

Laravel - Syntax error or access violation: 1064 You have an error in your SQL syntax

My problem is a little bit mystic. The out put of the sql code works if I try it with PHPmyadmin, but it doesn' work with laravel.

Here is the query builder code

$product= Product::leftJoin('ProductTransaction as PT',function($query){
                $query->on('PT.ProductId','=','Product.id');
                $todaysDate=Carbon::today()->toDateString();
                $query->Where(DB::raw("date(`PT`.`CreatedAt`)=$todaysDate"));

            })->Where(function($query) use($RetailerIds){
                if (count($RetailerIds)) {
                    $query->WhereIn('RetailerId',$RetailerIds);
                }
            })->Where(function($query)use($Genders){
                if (count($Genders)) {
                    $query->WhereIn('Gender',$Genders);
                }
            })->WhereHas("Stocks",function($query) use($Color){

                if ($Color!='') {
                    $query->WhereHas('Color',function($query) use($Color){
                        $query->Where('Color','like',"%$Color%");
                    });
                }

            })->Where(function($query) use ($q){

                if ($q!='') {
                    $query->Where('ProductTitle','like','%'.$q.'%');
                    $query->orWhere('Description','like','%'.$q.'%');
                }

            })->WhereHas('Stocks',function($query) {
                $query->Where('Stock','!=',-1);
                $query->WhereDate('CreatedAt','=',Carbon::today()->toDateString());
            })->WhereHas('ProductTransactions',function($query) use($MinDiscountPrice,$MaxDiscountPrice,$todaysMaxDiscountPrice,$todaysMinDiscountPrice){

                $query->WhereDate('Product.CreatedAt','=',Carbon::today()->toDateString());

                if ($todaysMinDiscountPrice!=$MinDiscountPrice) {
                    $query->Where('DiscountPrice','>=',$MinDiscountPrice);
                }

                if ($todaysMaxDiscountPrice!=$MaxDiscountPrice) {
                    $query->Where('DiscountPrice','<=',$MaxDiscountPrice);
                }
            })->With(['ProductTransactions','ProductImages','ProductCategory','Retailer','Stocks.Size'])->OrderBy($orderBy)->paginate(15);

Here is the error code that this query populates : 

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 '? where (select count(*) from `Stock` where `Stock`.`ProductId` = `Product`.`id`' at line 1 (SQL: select count(*) as aggregate from `Product` left join `ProductTransaction` as `PT` on `PT`.`ProductId` = `Product`.`id` and date(`PT`.`CreatedAt`)=2016-07-11 where (select count(*) from `Stock` where `Stock`.`ProductId` = `Product`.`id`) >= 1 and (`ProductTitle` like %test% or `Description` like %test%) and (select count(*) from `Stock` where `Stock`.`ProductId` = `Product`.`id` and `Stock` != -1 and date(`CreatedAt`) = 2016-07-11) >= 1 and (select count(*) from `ProductTransaction` where `ProductTransaction`.`ProductId` = `Product`.`id` and date(`Product`.`CreatedAt`) = 2016-07-11) >= 1)

But most interesting point is here, If I copy and paste the SQL code is in the Error text it works. Here is the SQL Code that I'm pasting it to PHPMyadmin

SELECT
    count(*) AS AGGREGATE
FROM
    `Product`
LEFT JOIN `ProductTransaction` AS `PT` ON `PT`.`ProductId` = `Product`.`id`
AND date(`PT`.`CreatedAt`) = 2016 - 07 - 11
WHERE
    (
        SELECT
            count(*)
        FROM
            `Stock`
        WHERE
            `Stock`.`ProductId` = `Product`.`id`
    ) >= 1
AND (
    `ProductTitle` LIKE % test %
    OR `Description` LIKE % test %
)
AND (
    SELECT
        count(*)
    FROM
        `Stock`
    WHERE
        `Stock`.`ProductId` = `Product`.`id`
    AND `Stock` != - 1
    AND date(`CreatedAt`) = 2016 - 07 - 11
) >= 1
AND (
    SELECT
        count(*)
    FROM
        `ProductTransaction`
    WHERE
        `ProductTransaction`.`ProductId` = `Product`.`id`
    AND date(`Product`.`CreatedAt`) = 2016 - 07 - 11
) >= 1
)

Upvotes: 0

Views: 1837

Answers (1)

aynber
aynber

Reputation: 23011

Your dates need to be quoted. Pass it in as a variable instead, or quote it:

$query->Where(DB::raw("date(`PT`.`CreatedAt`)='$todaysDate'"));

OR

$query->Where(DB::raw("date(`PT`.`CreatedAt`)=?",[$todaysDate])); // Preferable, so that the database can handle it

Upvotes: 1

Related Questions