PHP Yii2 MYSQL query giving wrong output

Question

I'm executing a PHP yii2 query to get records from the database but it's not working. But I directly executed the query in the MYSQL console then it gives me the expected output.

Normal SQL

SELECT * FROM `tbl_inbox` WHERE sender_id=778 AND recipient_id=736 OR sender_id=778 AND recipient_id=736 ORDER BY timestamp DESC LIMIT 1

Above query giving me expected result but when i change to Yii2 like :

$message=Inbox::find()->select(['message'])->where(['sender_id'=>$sender_id,'recipient_id'=>$recipient_id])->orWhere(['sender_id'=>$recipient_id,'recipient_id'=>$sender_id])->andWhere(['ad_id'=>$ad_id,'category'=>$category])->orderBy(['timestamp'=>SORT_DESC])->one();

It gives me the wrong result.

What is my logic :

I have a chat on my website and I need to get the last message order by timestamp. But sender_id and the recipient will be visa versa.

How to fix ?

Can you show what is the expected result and the wrong result? — Siim
– Siim, Commented Apr 19, 2021 at 5:50

Michal Hynčica · Accepted Answer · 2021-04-19 13:09:14Z

There are significant differences between the normal SQL version of the query and the Yii version.

1) Selected fields

The normal SQL query selects all fields, but Yii version selects only message field. This is because the normal SQL has SELECT * FROM ... but in Yii query you are calling select(['message']). If you want to select all fields you can leave the select() method call out or you can use select('*').

2) Your conditions are different

Considering operator priority your conditions in normal SQL are:

(sender_id=778 AND recipient_id=736) 
  OR (sender_id=778 AND recipient_id=736)

But in your Yii version of query:

(
  (sender_id=778 AND recipient_id=736)
  OR (sender_id=736 AND recipient_id=778)
) AND (
  ad_id=$ad_id AND category=$category
)

In your normal SQL the both sides of OR condition are same but in your Yii version the values for sender/recipient are swapped in the second argument of OR operator. Also there is extra part added by this call andWhere(['ad_id'=>$ad_id,'category'=>$category])

janavi · Accepted Answer · 2021-04-22 13:30:53Z

0

$message=Inbox::find()->select(['message'])->where(['sender_id'=>$sender_id,'recipient_id'=>$recipient_id])->orWhere(['sender_id'=>$recipient_id])->orWhere(['recipient_id'=>$sender_id])->andWhere(['ad_id'=>$ad_id,'category'=>$category])->orderBy(['timestamp'=>SORT_DESC])->one();

try this

I'm getting following sql

SELECT `message` FROM `user` WHERE ((((`sender_id`='2333') AND (`recipient_id`='23222')) OR (`sender_id`='23222')) OR (`recipient_id`='23333')) AND ((`ad_id`='10') AND (`category`='1')) ORDER BY `timestamp` DESC

edited Apr 22, 2021 at 13:30

answered Apr 22, 2021 at 13:23

janavi

263 bronze badges

Comments

Papp Péter · Accepted Answer · 2021-04-23 05:59:03Z

0

you can check raw sql easily, then it will be mostly straight forward how Yii Query builder works.

use following code:

$rawSql = Inbox::find()
...
->createCommand()->getRawSql();

I would change your code into this:

$message=Inbox::find()
    ->select(['message'])
    ->where([
        'and',
        [
            'or',
            ['sender_id'=>$sender_id,'recipient_id'=>$recipient_id],
            ['sender_id'=>$recipient_id,'recipient_id'=>$sender_id]
        ],
        ['ad_id'=>$ad_id,'category'=>$category]
    ])
    ->orderBy(['timestamp'=>SORT_DESC])->one();

answered Apr 23, 2021 at 5:59

Papp Péter

1621 silver badge4 bronze badges

Collectives™ on Stack Overflow

PHP Yii2 MYSQL query giving wrong output

3 Answers 3

1) Selected fields

2) Your conditions are different

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1) Selected fields

2) Your conditions are different

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related