Left Join In Search

Yii 1.1 General Discussion
1

I have participants with balances and maybe pending transactions. I need a search that shows current and pending amounts. My LEFT JOIN is not working. I only get participants that have pending transactions. Any hints?

Participant model




public function relations()

{

    return array(

        // 'transaction' => array(self::HAS_MANY, 'Transaction', 'participant_ID'),

        'transaction' => array(self::HAS_MANY, 'Transaction', 'participant_ID','joinType'=>'LEFT JOIN'),

    );

}


public function searchPending()

{

    $criteria=new CDbCriteria;

    // $criteria->with = array('transaction');


    $criteria->compare('t.participant_ID',$this->participant_ID);

    $criteria->compare('t.employer_ID',$this->employer_ID);

    $criteria->compare('t.participant_name',$this->participant_name,true);

    $criteria->compare('t.pw_balance',$this->pw_balance,true);


    $criteria->mergeWith(array(

        'join' => 'LEFT JOIN transaction tt ON t.participant_ID = tt.participant_ID',

        'condition' => 'tt.status = "Pending"'

    ));


    $criteria->select = array('t.participant_ID, t.participant_name, t.employer_ID, mm_balance, sh_balance, SUM(tt.dollars) as pending');

    $criteria->group = 't.participant_ID';


    return new CActiveDataProvider($this, array(

        'criteria'=>$criteria,

    ));

}

Thanks

2

Move the condition from WHERE to JOIN:




$criteria->mergeWith(array(

    'join' => 'LEFT JOIN transaction tt ON t.participant_ID = tt.participant_ID AND tt.status = "Pending',

  ));
3

Worked, thanks