I already spent weeks trying to convert this query to yii2. This is what I have in Yii 1 and it works
$rates=Rates::model()->findAll("status = 'active' AND master_account = $account AND branch = $branch AND location_from = '$location_from' AND location_to = '$location_to' AND CAST('$order_placed_time' AS time) BETWEEN book_from AND book_by OR (NOT CAST('$order_placed_time' AS time) BETWEEN book_by AND book_from AND book_from > book_by)");
I need to have that same query running under Yii2 but it’s not working. This is what I have so far:
I’m afraid you already have a flaw in the original query.
The sql above is equivalent to the following. Is it really what you meant?
[sql]
where
(
status = 'active'
AND
master_account = $account
AND
branch = $branch
AND
location_from = '$location_from'
AND
location_to = '$location_to'
AND
CAST('$order_placed_time' AS time) BETWEEN book_from AND book_by
)
OR
(
NOT CAST('$order_placed_time' AS time) BETWEEN book_by AND book_from
AND
book_from > book_by
status = 'active'
AND
master_account = $account
AND
branch = $branch
AND
location_from = '$location_from'
AND
location_to = '$location_to'
AND
(
CAST('$order_placed_time' AS time) BETWEEN book_from AND book_by
OR
(
CAST('$order_placed_time' AS time) NOT BETWEEN book_by AND book_from
AND
book_from > book_by
)
)
and my order placed is being 17:05:00 which means that it should be returning one result only but its returning nothing. I even tried to do a query just to retrieve between from and to and also returning nothing.
I’m also converting to time before my query $order_placed_time = date(‘H:i:s’,strtotime($_GET[‘goods_ready_date’]));
Thanks for your help. I managed here and ended up using this:
$rates = Rates::find()->where("status = 'active' AND master_account = '$account' AND branch = '$branch' AND location_from = '$location_from_id' and location_to = '$location_to_id' AND CAST('$order_placed_time' AS time) BETWEEN book_from AND book_by")->all();