Error Trying To Get Table In Multiple Db Relation With Cdbcriteria

Hello to everyone, this is my first post here, I read the forum rules so I hope getting this right.

In a project I have a multiple DB configuration following the steps of the wiki: Multiple Databases and Multiple Domains

Actually it works great (and have more than 4 DB), even I can write this in the view a column of a CGridView:

  array('name'=>'ColumnName','value'=>'$data->rel1->rel2->rel3->columnName'),

Where rel3 is in another DB.

My problem is when I want to order some data when creating a CActiveDataProvider:

$valDataProvider =new CActiveDataProvider('Class1',array(

                            'criteria'=>array('with'  => array('relClass1',relClass1.relClass2'

                                                              ),

                                              'together'=>true,

                                              'order' => 'relClass1.relClass2.columnName DESC',

                                              )

                        ));

This gets me the error :

CDbCommand falló al ejecutar la sentencia SQL: SQLSTATE[HY000] [-1] Invalid object name 'dbo.table1'.

Translated it says:

CDbCommand failed to execute the SQL Query: SQL:SQLSTATE[HY000] [-1] Invalid object name 'dbo.table1'

Now for what I have discovered, can’t find the DB table (because is searching in DB1 instead of DB2).

I searched in Yii code but actually I just suppose that happens two things:

• I did something wrong

• Yii is generating a bad sql query

I hope I have make my problem clear and I would really appreciate a little bit of help, thank you everyone

Extra Info:

Yii version : 1.1.10

DB Manager : Microsoft SQL Server 2008

EDIT:

Browsing Yii code, it is a difference between eager loading and lazy loading (the next statements perhaps are incomplete or incorrect but well I’m trying to undestand what is happening), when is a eager loading, in CActiveFinder, when is contructing the tree, it goes and ask the schema to the CDbCommandBuilder of each model and then CDbSchema returns the schema and name of the table and the connection is assumed of the model where was call. So then when the tree is passed to CDbCommand and is constructing the query, in the method

From (see line 695 from de CDBCommand source code [can’t put link becasuse is my first post]) it just joining each part of the string but not its DB, so then when try to make the query; actually I already see the log and it gave me this:

CDbCommand::fetchColumn() failed: SQLSTATE[HY000] [-1] Invalid object name

'dbo.anotherDBTbName'.. The SQL statement executed was: SELECT COUNT(DISTINCT

[t].[col1]) FROM [dbo].[tb1] [t]  LEFT OUTER JOIN

[dbo].[tb2] [codigoOperadoresRel] ON

([t].[tb1]=[codigoOperadoresRel].[col1])  LEFT OUTER

JOIN [dbo].[anotherDBTbName] [innoCanAltClientes] ON

([codigoOperadoresRel].[col1]=[innoCanAltClientes].[col1])

So I’m guessing, is this a bug?

Dear Friend

Following is my understanding. Correct me If I am wrong.

When you are fetching data along with relational tables with method with, YII assignes relation name as table alias.

suppose we have two models: Author and Post.

One author has many posts.(relation)

From model Author…

$dataProvider =new CActiveDataProvider('Author',array(

                            'criteria'=>array('with'  => array('posts' ),

                                              'together'=>true,

                                              'order' => 'posts.create_time DESC',//posts is the table alias for table post.

                                              )

                        ));

If you are assigning

'order' => 'author.posts.create_time DESC'

It means that there is no table in that name.

Regards

Thank you for responding, actually I know that is the alias (I change the names of what actually are because is a job DB), and Yii understand the relationAlias and change it in the query with the table name but without his proper DB name, perhaps I don’t explain so perhaps a little of code will make me clear( I think one piece of code won’t do harm.)

In the method controller it is:

           $comisionesRemesasDataProvider=new CActiveDataProvider('TMComisionesRemesas',array(

                            'criteria'=>array('with'  => array('codigoOperadoresRel',

                                                               'codigoOperadoresRel.innoCanAltClientes'

                                                              ),

                                              'together'=>true,

                                              'order' => 'codigoOperadoresRel.innoCanAltClientes.nombre DESC',

                                              )

                        ));

in the models

[TMComisionesRemesas]

class TMComisionesRemesas extends DB1ActiveRecord

{

    	public function getDbConnection() {

        return self::getDB1DbConnection();}


...


public function relations()

	{

		return array(

                    'codigoOperadoresRel' => array(self::BELONGS_TO, 'TMOperadores', 'codigoOperador'),

		);

	}

}

[TMOperadores]

class TMOperadores extends DB1ActiveRecord

{

    	public function getDbConnection() {

        return self::getDB1DbConnection();}


...


public function relations()

	{

		return array(

			'solicitudes' => array(self::HAS_MANY, 'TMSolicitudes', 'codigoOperador'),

                        'comisionesRemesas' => array(self::HAS_ONE, 'TMComisionesRemesas', 'codigoOperador'),

                        'innoCanAltClientes' => array(self::BELONGS_TO, 'Clientes', 'codigoCliente'),

                        //Clientes belongs to another DB

		);

	}

}

[Clientes]

class Clientes extends DB2ActiveRecord

{

    	public function getDbConnection() {

        return self::getDB2DbConnection();}


...


public function relations()

	{

		return array(

			

			...


                        'tmoperadores' => array(self::HAS_MANY, 'TMOperadores', 'codigoCliente'),


                        ...                        

		);

	}

}

and the error says

CDbCommand::fetchColumn() failed: SQLSTATE[HY000] [-1] Invalid object name

'dbo.clientes'.. The SQL statement executed was: SELECT COUNT(DISTINCT

[t].[idComision]) FROM [dbo].[comisionesRemesas] [t]  LEFT OUTER JOIN

[dbo].[operadores] [codigoOperadoresRel] ON

([t].[codigoOperador]=[codigoOperadoresRel].[codigoOperador])  LEFT OUTER

JOIN [dbo].[clientes] [innoCanAltClientes] ON

([codigoOperadoresRel].[codigoCliente]=[innoCanAltClientes].[codigoCliente])

The way I put the with I based on the comments that CDBCriteria page have, and the fact that in the error Yii actually try to make a JOIN with the real name of the table of the alias is telling me that that is part is correct. I hope now I’m more clear, sorry for so many code in this post haha I’m a little frustrated.

Thank you for making the time to read this.