createCommand question

Yii 1.1 General Discussion
1

The following SQL query returns two different results depending on the origin.

[sql]SELECT

p.COMMENTS, v.COMMENTS

FROM PRODUCTS p

LEFT OUTER JOIN VENDOR v ON v.VEN_UID = p.VEN_ID[/sql]

Running the query directly via the server (Oracle) provides the following table results. This is what I am looking for in terms of results:




col:| --- COMMENTS_1--- | --- COMMENTS_2 --- |

row:| Product Comments  | Vendor Comments    |

However, running via Yii returns the following table results.

[sql]Yii::app()->db->createCommand($sql)->query();[/sql]

Result:




col:| --- COMMENTS --- |

row:| Vendor Comments  |

My Yii db connection (if it matters)




	'db'=>array(

            'class'=>'CDbConnection',            

            'connectionString'=>'oci:dbname=//192.168.1.1',            

            'username'=>$username,

            'password'=>$password,                        

            'enableParamLogging' => true,            

        )

In terms of the query, what am I doing wrong? I’ve searched the forum, but was not able to locate posts of this particular topic.

Thanks in advance.

2

Sometimes, the weirdest things happens when we do queries isn’t it? Nevertheless, in order to find the real problem for for that, lets breakdown the SQL and make it easy for the engines to understand. I am telling you, sometimes PDO is funny.

Do not use table aliases even though your query is correct. Let’s see how the command is executed then.




SELECT 

PRODUCTS.COMMENTS, VENDOR.COMMENTS 

FROM PRODUCTS 

LEFT OUTER JOIN VENDOR ON VENDOR.VEN_UID = PRODUCTS.VEN_ID

By the way, where is the alias ‘m’ coming from?

3

Whoops, posting typo! Corrected.

I’ll give the non-alias SQL version a go and see what happens. My initial motivation behind the alias style was to keep the query itself to a minimum as my queries do get quite large in size…

I’m mainly using this approach for statistical report reasons.

4

Alas, your suggestion generated the same results in the end. Thanks Antonio.

Any other ideas or clues that might provide a solution?

5

That is very weird,

Try to alias the column names:




SELECT 

PRODUCTS.COMMENTS AS COMMENTS_1, VENDOR.COMMENTS AS COMMENTS_2

FROM PRODUCTS 

LEFT OUTER JOIN VENDOR ON VENDOR.VEN_UID = PRODUCTS.VEN_ID
6

Bingo! That did the trick. I created a random generator for the alias just to test and it worked as expected. I’ll likely switch from random characters to a counter method in the end though.

Many thanks Antonio!

7

Congratulations man!

I told you, PDO is funny sometimes… But somehow, was normal you cannot have two columns be named the same way.

Best!