Convert query from Yii 1 to 2.0

Yii 2.0 General Discussions
1

Hi Guys,

Please help me out because I’m going nuts here. It must be something that I’m missing. I’m converting my project in Yii 1.x to 2.0 and in Yii 1.x I use this query:




$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)");

and I converted to Yii 2.0 this way:




$rates = Rates::find()->where("status = 'active' AND master_account = $account AND branch = $branch AND

location_from = '$region_from_id' AND location_to = '$region_to_id' AND

CAST('$goods_ready_date' AS time) BETWEEN book_from AND book_by OR

(NOT CAST('$goods_ready_date' AS time) BETWEEN book_by AND book_from AND book_from > book_by)");

But it’s not returning anything to me. What am I missing? Please help me out.

Thanks

2

You may need to add something like


  $rates = Rates::find(...)->all();

See if that helps

3

Thanks but it still didn’t work.

4

Check this.




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

            'and',

            [

                'status' => 'active', 

                'master_account' => $account, 

                'branch' => $branch, 

                'location_from' => $location_from, 

                'location_to' => $location_to

            ],

            [

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

Bizley thank you for your help but it didn’t work. I’m getting an empty array. I tried to change a little bit what you suggested and still no result. Any other suggestion? I would really appreciate.

6

Are you sure this query must return results?

Call the query itself on your database and see if it returns any data.

To get the query add ->createCommand()->sql to the part without ->all()

PS. The weird part of your statement is “NOT CAST(’$order_placed_time’ AS time) BETWEEN”. I did “CAST(’$order_placed_time’ AS time) NOT BETWEEN”. What’s up with that?

7

Bizley,

That works. Thank you very much, I just added the NOT CAST and it worked perfect. Thank you so much!!!!

8

Actually it did not work. It’s not considering the book by and book from :confused:

I have this value in my database: https://www.dropbox.com/s/iwq96xkkyyfbycw/Screenshot%202015-09-13%2022.51.49.png?dl=0

and in my query, the value of $order_placed_time is 09:00:00 but somehow is returning the value of the one in the picture. That one shouldn’t return because the book from and book by is not between the 09:00:00

Does it makes sense?

Any thoughts?

Thanks for the help.

9

My point is: what is “not cast”? I’m risking exposing my lack of knowledge in this matter but I’ve never heard of it. That is why I assumed you mean “cast() not between”

10

@Bizley

I just tested that ‘not cast() between a and b’ worked the same way as ‘cast() not between a and b’, although it may be db specific (I used MySQL 5.6.24).

@dudunegrinhu

Could you show us the actual sqls that Yii 1.1 and 2.0 have constructed?

11

@softark

You are right. It’s the same with 5.5.29.

12

It seems to me a problem of logical operators … I mean, "and", "or" and "not".

The sql constructed by Yii 2 seems to have a condition that should return no row, something like "a and not a". So @dudunegrinhu should examine the actual sqls.

[EDIT]

Also he seems to have altered the table schema a little.

13

The original query so asks for brackets on two last conditions.