Is It Possible To Create A Join Within Cactivedataprovider With Non Related Table

Yii 1.1 General Discussion
1

Hi

Is it possible to add a third,join into CActiveDataProvider when this table isn’t directly related. Or would I need to build the query another way? I did try via the ‘join’ parameter but it didn’t seem to work.

Thanks

Jonny

2

Give us more details, like your model names and what relations() method return.

3

Hi,

Well I’m joining the table ‘Status’, with ‘Item’. Status has a relation called to ‘Item’ as status will be an inner join with item.

I have a third table ‘Interest’ that I want to left join, that has a column ‘user_id’, that will be present in the Status and Item tables if a there is a record present.

4

You didn’t answer my question. How does your models look? Did you used gii to create them?

5

I used Giix and the model




'Status'

public function relations() {

		return array(


			'item' => array(self::BELONGS_TO, 'Item', 'item_id'),

		);

	}

‘Item’




public function relations() {

		return array(

'status' => array(self::HAS_MANY, 'Status', 'item_id'),

);

	}







'Interest'

	public function relations() {

		return array(

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

			

		);

	}

But all those tables have a user_id column

6

So those three models are related to the User? You can do two things:

  1. Declare an extra relation using ‘through’ pointing to the user relation.

  2. Declare a custom direct relation by specifying custom foreign keys. They don’t have to match those in the database.

Some examples for the Status model, adjust to your case:




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

'interestsThrough' => array(self::HAS_MANY, 'User', 'user_id', 'through'=>'user'),

'interestsDirect' => array(self::HAS_MANY, 'User', array('user_id'=>'user_id')),
7

Thanks

8

You can try the join using criteria.


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

    'criteria' => array(

         // :

        'with' =>'item',

         // :

        'join' => 'INNER JOIN interest i ON i.user_id=t.user_id',

    )

));
9

I tried that before I posted and I couldn’t seem to get thte join to occur