samedi 4 février 2017

Laravel 5.1 - Eloquent parameter bindings being applied in wrong order?

I have the following update query which consists of a sub-query join (I had to use DB:Raw as I was unable to find any documentation to accomplish via any other method - unless someone can show me some other way to achieve it).

However, because the table/model being updated uses timestamps, a value for updated_at is also being added and messes up the order of the parameter bindings?

OrderItemProduct::from('order_item_product as t1')
        ->join(DB::Raw('(
           SELECT t1.product_id as product_id
                , MIN(t1.created_at) as created_at
                , MIN(t1.order_item_id) as order_item_id
             FROM order_item_product t1
      INNER JOIN order_items t2 on t1.order_item_id = t2.id
      INNER JOIN orders t3 on t2.order_id = t3.id
           WHERE t1.product_id = ?
             AND t1.available > 0
             AND TIMESTAMPADD(DAY,t1.valid_days,t1.created_at) > now()
             AND t3.user_id = ?
        GROUP BY t1.product_id) AS t2'), function($join)
        {
                $join->on('t1.order_item_id', '=', 't2.order_item_id');
                $join->on('t1.created_at', '=', 't2.created_at');

         })
        ->setBindings([$productId, $userId])
        ->decrement('t1.available');

Below is what I end up with when I die n dump the query log:

array:1 [▼
   0 => array:3 [▼
     "query" => """
       update `order_item_product` as `t1` inner join (\r\n
                 SELECT t1.product_id as product_id\r\n
  \t                  , MIN(t1.created_at) as created_at\r\n
  \t\t                , MIN(t1.order_item_id) as order_item_id\r\n
  \t\t             FROM order_item_product t1\r\n
             INNER JOIN order_items t2 on t1.order_item_id = t2.id\r\n
             INNER JOIN orders t3 on t2.order_id = t3.id\r\n
                  WHERE t1.product_id = ?\r\n
                    AND t1.available > 0\r\n
                    AND TIMESTAMPADD(DAY,t1.valid_days,t1.created_at) > now()\r\n
  \t                AND t3.user_id = ?\r\n
               GROUP BY t1.product_id) AS t2 ON `t1`.`order_item_id` = `t2`.`order_item_id` 
                   AND `t1`.`created_at` = `t2`.`created_at` 
                   SET `t1`.`available` = `t1`.`available` - 1, `updated_at` = ?
       """
     "bindings" => array:3 [▼
         0 => "2017-02-04 09:41:22"
         1 => 1
         2 => 1
     ]
     "time" => 2.9
   ]
]

As you can see inside the bindings array a value for updated_at is automatically added by eloquent at index 0 and so it's ending up being bound to the first parameter placeholder inside the query which is incorrect.

How can I fix this?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire