Struggling with yii2 query

dudunegrinhu · June 17, 2016, 3:48pm

Guys,

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:





$rates = Rates::find()->where([

			'and',

			[

				'status' => 'active',

				'master_account' => $account,

				'branch' => $branch,

				'location_from' => $location_from_id,

				'location_to' => $location_to_id

			],

			[

				'and',

				[

					'between',

					new \yii\db\Expression('NOT CAST(:order_placed_time AS time)'),

					'book_from',

					'book_by'

				],

				[

					'and',

					[

						'between',

						new \yii\db\Expression('CAST(:order_placed_time AS time)'),

						'book_by',

						'book_from'

					],

					[

						'>',

						'book_from',

						'book_by'

					]

				]

			]

		])->addParams([':order_placed_time' => $order_placed_time])->all();

but it seems that the query is not validating the between dates. Can anyone help me please? I’m desperate.

softark · June 17, 2016, 10:12pm

dudunegrinhu:




$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’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 = &#036;account


AND 


branch = &#036;branch


AND 


location_from = '&#036;location_from'


AND 


location_to = '&#036;location_to'


AND 


CAST('&#036;order_placed_time' AS time) BETWEEN book_from AND book_by

)

OR

(

NOT CAST('&#036;order_placed_time' AS time) BETWEEN book_by AND book_from


AND 


book_from &gt; book_by

)

[/sql]

softark · June 17, 2016, 10:22pm

[sql]

where

(

status = 'active'


AND 


master_account = &#036;account


AND 


branch = &#036;branch


AND 


location_from = '&#036;location_from'


AND 


location_to = '&#036;location_to'


AND 


(


  CAST('&#036;order_placed_time' AS time) BETWEEN book_from AND book_by


  OR


  (


    CAST('&#036;order_placed_time' AS time) NOT BETWEEN book_by AND book_from


    AND 


    book_from &gt; book_by


  )


)

)

[/sql]

The above could be written like:




$rates = Rates::find()->where([

    'and',

    [

        'status' => 'active',

        'master_account' => $account,

        'branch' => $branch,

        'location_from' => $location_from_id,

        'location_to' => $location_to_id

    ],

    [

        'or',

        [

            'between',

            new \yii\db\Expression('CAST(:order_placed_time AS time)'),

            'book_from',

            'book_by'

        ],

        [

            'and',

            [

                'not between',

                new \yii\db\Expression('CAST(:order_placed_time AS time)'),

                'book_by',

                'book_from'

            ],

            [

                '>',

                'book_from',

                'book_by'

            ]

        ]

    ]

])->addParams([':order_placed_time' => $order_placed_time])->all();

softark · June 17, 2016, 10:39pm

Or do we have to have 2 place holders? I’m not sure:




$rates = Rates::find()->where([

    ...

            new \yii\db\Expression('CAST(:order_placed_time AS time)'),

    ...

                new \yii\db\Expression('CAST(:order_placed_time2 AS time)'),

    ...

])->addParams([

    ':order_placed_time' => $order_placed_time,

    ':order_placed_time2' => $order_placed_time

])->all();

dudunegrinhu · June 18, 2016, 1:37am

Softark,

I tried your suggestion but it seems that it’s not obeying the between time.

I have these data in my db: https://www.dropbox.com/s/2i79rfgcfky9cy1/Screenshot%202016-06-18%2011.34.33.png?dl=0

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’]));

softark:

Or do we have to have 2 place holders? I’m not sure:




$rates = Rates::find()->where([

    ...

            new \yii\db\Expression('CAST(:order_placed_time AS time)'),

    ...

                new \yii\db\Expression('CAST(:order_placed_time2 AS time)'),

    ...

])->addParams([

    ':order_placed_time' => $order_placed_time,

    ':order_placed_time2' => $order_placed_time

])->all();

dudunegrinhu · June 18, 2016, 1:42am

If I only do this:




$rates = Rates::find()->where([

			'and',

			[

				'status' => 'active',

				'master_account' => $account,

				'branch' => $branch,

				'location_from' => $location_from_id,

				'location_to' => $location_to_id

			],

])->all();

than it works but as soon as I do the between time than it doesn’t work.

softark · June 18, 2016, 3:09am

Hmm, we have to check and see what sql Yii has constructed.

dudunegrinhu · June 18, 2016, 3:17am

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();

and it works!!! Thank you

softark · June 18, 2016, 3:31am

Hmm, a prepared statement in a db expression seems to be the problem.

softark · June 18, 2016, 3:43am

Ah, OK.

Parameters should be specified with the expression itself.

http://www.yiiframework.com/doc-2.0/yii-db-expression.html

Please try this:




$rates = Rates::find()->where([

    'and',

    [

        'status' => 'active',

        'master_account' => $account,

        'branch' => $branch,

        'location_from' => $location_from_id,

        'location_to' => $location_to_id

    ],

    [

        'or',

        [

            'between',

            new \yii\db\Expression('CAST(:order_placed_time AS time)', 

                 [':order_placed_time' => $oerder_placed_time]),

            'book_from',

            'book_by'

        ],

        [

            'and',

            [

                'not between',

                new \yii\db\Expression('CAST(:order_placed_time AS time)', 

                     [':order_placed_time' => $oerder_placed_time]),

                'book_by',

                'book_from'

            ],

            [

                '>',

                'book_from',

                'book_by'

            ]

        ]

    ]

])->all();