CODEWITHSUNDEEP
phpmysqlsqlyii
J.K.A.
J.K.A.

Reputation: 7404

Yii CDBCriteria Complex Join Query

I have 3 tables:

  1. bank
  2. bank_details
  3. bank_bank_details

Relation between them:

bank model:

public function relations() {
        return array(
            'bankBankDetails' => array(self::HAS_MANY, 'BankBankDetails', 'bank_id'),
        );
}

bank_details model:

public function relations() {
        return array(
            'bankBankDetails' => array(self::HAS_MANY, 'BankBankDetails', 'bank_details_id'),
        );
    }

bank_bank_details model: 

public function relations()
    {
        return array(
            'bank' => array(self::BELONGS_TO, 'Bank', 'bank_id'),            
            'bankDetails' => array(self::BELONGS_TO, 'BankDetails', 'bank_details_id'),
        );
    }

I want to fetch bank details like bank_name, ifsc etc info in admin view of bank_details model.

The normal SQL query I have generated which works perfectly fine:

SELECT b.name
FROM bank b
LEFT JOIN bank_bank_details bbd ON bbd.bank_id = b.bank_id
LEFT JOIN bank_details bd ON bd.bank_details_id = bbd.bank_details_id
WHERE bd.bank_details_id = bbd.bank_details_id
LIMIT 0 , 30

Now I just wants to integrate this with Yii CDBCriteria but its not working for me. Please check the code below:

public function search() {
        $criteria = new CDbCriteria;    
//        select b.name
//        from bank b
//        left join bank_bank_details bbd
//        on bbd.bank_id = b.bank_id
//        left join bank_details bd on bd.bank_details_id = bbd.bank_details_id
//        WHERE bd.bank_details_id = bbd . bank_details_id;

        $criteria->compare('bank_details_id', $this->bank_details_id);
        $criteria->compare('first_holder_name', $this->first_holder_name, true);
        $criteria->compare('nominee1', $this->nominee1, true);
        $criteria->select = 'b.name';
        $criteria->join = 'LEFT JOIN bank_bank_details bbd ON bbd.bank_id = b.bank_id ';
        $criteria->join .= 'LEFT JOIN bank_details bd ON bd.bank_details_id = bbd.bank_details_id';
        $criteria->condition = 'bd.bank_details_id = bbd.bank_details_id';

        return new CActiveDataProvider($this, array(
            'criteria' => $criteria,
            'pagination' => array(
                'pageSize' => 10,
            ),
        ));
    }

Error: Error 500 Found CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'b.bank_id' in 'on clause'

Any help would be appreciated.

Upvotes: 0

Views: 16785

Answers (4)

raphaeltm
raphaeltm

Reputation: 817

As @Evgeniy mentioned, you will need to set the alias using $criteria->alias = 'b';

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#alias-detail

You might also want to look into using CDbCriteria::with you can use this to select related models.

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#with-detail

If you use the above, make sure you also use CDbCriteria::together correctly. To select all related models at once, set it to true;

http://www.yiiframework.com/doc/api/1.1/CDbCriteria#together-detail

Upvotes: 0

CreatoR
CreatoR

Reputation: 1652

Try:

public function search() {
    $criteria = new CDbCriteria;    
    $criteria->compare('bank_details_id', $this->bank_details_id);
    $criteria->compare('first_holder_name', $this->first_holder_name, true);
    $criteria->compare('nominee1', $this->nominee1, true);
    $criteria->select = 'bank.name';
    $criteria->with = array(
         'bankBankDetails' => array('joinType'=>'LEFT JOIN'),
         'bankBankDetails.bank' => array('joinType'=>'LEFT JOIN'),
    );
    $criteria->addCondition('t.bank_details_id = bankBankDetails.bank_details_id');

    return new CActiveDataProvider($this, array(
        'criteria' => $criteria,
        'pagination' => array(
            'pageSize' => 10,
        ),
    ));
}

Main table (which is in FROM) has alias t and after compare you already having some condition, than you need to add to existing, not rewrite

UPDATE

If you do query from model bank_details then the main table (in FROM) must be bank_details. Query will be:

SELECT b.name
FROM bank_details bd
LEFT JOIN bank_bank_details bbd ON bd.bank_details_id = bbd.bank_details_id
LEFT JOIN bank b bbd ON bbd.bank_id = b.bank_id
WHERE bd.bank_details_id = bbd.bank_details_id
LIMIT 0 , 30

Upvotes: 1

Evgeniy
Evgeniy

Reputation: 11

You need to set alias to your table like this: $criteria->alias='b'; or use default alias "t" instead of "b"

read more at http://www.yiiframework.com/doc/api/1.1/CDbCriteria#alias-detail

Upvotes: 1

coolguy
coolguy

Reputation: 7954

here $criteria->select = 'b.name'; You are selecting only bank name and no id is selected

Not sure with the yii format something like

$criteria->select = 'b.name,b.bank_id';

or

$criteria->select = array('b.name,b.bank_id');

Upvotes: 0

Related Questions