Join Query and Condition with CActiveRecord got error.

Yii 1.1 General Discussion
1

HI Friends,

I try to Join more than two tables with condition applied for the join table in the YII version [font="Verdana"][size="2"]1.0.7[/size][/font] .Please take a look at the following code.

Th relation for the table in tbl_messages_thread





'tbl_messages_users' => array(self::HAS_MANY, 'TblMessagesUsers', 'thread_id','together'=>true),

The tbl_messages having the relation




'tbl_messages_thread' => array(self::HAS_MANY, 'TblMessagesThread', 'message_id'),










          $useremailid="abc@website.com";

          $criteria=new CDbCriteria;

          $criteria->condition="tbl_messages_users.user_id='".$useremailid."'";   

          $models=TblMessages::model()->with('tbl_messages_thread','tbl_messages_thread.tbl_messages_users','tbl_messages_thread.tbl_messages_files')->findAll($criteria);

 			return $models;

When I am executing the above query it throws the following error.

[color="#800000"][font="Verdana"]CDbCommand failed to execute the SQL statement: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "tbl_messages_users"[/font][/color][color="#800000"][font="Verdana"]LINE 1: …es"."flag" AS "t0_c3" FROM "tbl_messages" WHERE ("tbl_messa…[/font][/color]

[font="Verdana"]I applied the double quotes to the Join too. Like below.[/font]

[font="Verdana"]




 $criteria->condition='"tbl_messages_users".user_id='".$useremailid.'"';

[/font]

But its also Not working.How I need to apply the condition for the above query.

2

Is there any one?..I am still looking for help…

3

I’ve just had the same problem… I think it’s the [font=“Courier New”]tablenames[/font] used in the[font=“Courier New”] with()[/font].

What database and operating system is your site running on?

I’m using linux and postgres, which is case-sensitive, including [font=“Courier New”]tablename[/font] is different to [font=“Courier New”]“tablename”[/font]. I suspect you’ll need to put double-quotes around your tablenames mentioned in the [font=“Courier New”]with()[/font].

Like so:

[font=“Courier New”] $models=TblMessages::model()->with(‘tbl_messages_thread’,’“tbl_messages_thread”.tbl_messages_users’,’“tbl_messages_thread”.tbl_messages_files’)->findAll($criteria);[/font]