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