Complicated and ordered JOIN using Yii

Yii 1.1 General Discussion
1

Hello.

I would like to use Yii to create JOIN and order data by column in joined table.

I have 1 table with Primary Key and Foreign Key. Let’s say it is table “user2post”.

PK = post_id, user_id

FK = user_id

Second table has only PK. It’s table “user”.

PK = user_id

column_1 = username

I need to get everything that’s in table “user2post” and add user names. Normal query would look like this:




SELECT user2post.post_id, user.name

FROM user2post 

INNER JOIN user 

ON user2post.user_id = user.user_id

ORDER BY user.name

Now I try to do this using Yii:

In Yii I added relation from "user2post" to "user" table. It works.




'getUser' => array(self::BELONGS_TO,'user','user_id')

Now I use it like this:




$criteria=new CDbCriteria;

$criteria->together = 'getUser';

(I know that I can use "with" but in my case it does not work)




user2post::model()->findAll($criteria);

It works.

… and I would like to add




$criteria->order = 'user.name';

But it does not work. I get this error:

– The column prefix ‘user’ does not match with a table name or alias name used in the query.

"user" is name of table and name of model.

Can you help please?

And doesn’t exist any tutorial that would explain problems like this? I know that there is this page:

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-options

But it does not explain anything important. Just obvious things :(

2

Please use code tags "<>" in the editor toolbar. It makes your code easier to read.

I suggest yo try this (not sure the use of getUser is a good idea)




$criteria=new CDbCriteria;

$criteria->with = 'getUser'; 

$criteria->together = true; 

user2post::model()->findAll($criteria);

and then




$criteria->order = 'getUser.name';

(not tested)

/Tommy

Edit: btw, what do you mean with "PK = post_id, user_id"? Two segments?

3

this case will not work

CDbCriteria its schema of db connection ,it dosen`t matter in which case will be setted it all ways create object with arrays , and all criteria have to be in new CDbCriteria object before ar findAll call




$criteria=new CDbCriteria;


$criteria->with = array('getUser'=>array('order'=>'getUser.name')); //with is array property or 




user2post::model()->findAll($criteria);

or define in relation ordering

4

Hi. Thanks for tips to all.

Following worked for me:




$criteria->with = 'getUser'; 

$criteria->together = true;

To Igor:

PK = post_id, user_id

means that Primary key consists of 2 columns = composite primary key.

Each row has unique combination of post_id and user_id.

To Tri:

Code tags added.

5

I know what it means , i quoted on tri post… because criteria is under the call

6

Sorry, I wrote wrong name…

7

And one more thing.

Join works, but pagination stopped working because of the "with". Important role playes ordering.

I use this:




$criteria->with = 'getUser'; 

$criteria->together = true;

But if I want to show 4th page like this:




$criteria->limit = 10; 

$criteria->offset = 30;

… than Yii does not know how to order rows (by which columns) so offset does not work. Because in my DB (MS-SQL 2000) is "offset" done using this construction:




SELECT * FROM 

  (SELECT TOP 25 * FROM 

    (SELECT TOP 75 * FROM table

      ORDER BY pk_col asc

     ) 

   ORDER BY pk_col desc

    ) 

ORDER BY pk_col

But Yii (I do not why) can not order tables - althought PK is defined in DB and in Model so it could be used.

Pagination does not work if I use:




$criteria->with = 'getUser';

Does this have solution?

I tried to put ordering into Relation and to DefaultScope, but SQL than replies, that column is inknown, because Yii renames columns in SQL query like this:




SELECT [t].[username] AS [t0_c0] FROM [table] [t]

:frowning: