Help with LEFT JOIN

Hello Guys,

I’m trying to make a left join work, but can’t seem to get it right…

Here’s the query code:




$with_condition = array(

  'alias' => 'company_accounts',

  'select' => 'company,logo,logo_width,logo_height',

  'joinType' => 'LEFT JOIN',

  'condition' => 't.company_id = company_accounts.id',

);


$article = FranchiseInformationArticles::model()->with(array('company_accounts' => $with_condition))->live()->findByPk($id, array('select' => 'id,title,text,company_id',));



In the FranchiseInformationArticles model I have:




public function relations()

{

  return array(

    'company_accounts' => array(self::HAS_MANY, 'CompanyAccounts', 'id'),

  );

}



Now… I can’t access ‘company,logo,logo_width,logo_height’. Any ideas what am I doing wrong ?

Kind Regards,

Marian




$with_condition = array(

  'alias' => 'company_accounts',

  'select' => 'company_accounts.company,company_accounts.logo,company_accounts.logo_width,company_accounts.logo_height',

  'joinType' => 'LEFT JOIN',

  'condition' => 't.company_id = company_accounts.id',

);


$article = FranchiseInformationArticles::model()->with(array('company_accounts' => $with_condition))->live()->findByPk($id, array('select' => 'id,title,text,company_id',));




if(!empty($article->company_accounts))

{

   foreach($article->company_accounts AS $ca)

   {

     echo $ca->company.' '.$ca->logo;

   }

}



Also, you have a relation set up for this, so you don’t actually need the with condition, so doing directly:




$article = FranchiseInformationArticles::model()->live()->findByPk($id, array('select' => 'id,title,text,company_id',));


if(!empty($article->company_accounts))

{

   foreach($article->company_accounts AS $ca)

   {

     echo $ca->company.' '.$ca->logo;

   }

}



Will work just fine.

I have managed to get the query working by modifying the code like this:


$with_condition = array(

  'select' => 'company,logo,logo_width,logo_height',

  'joinType' => 'LEFT JOIN',

);


$article = FranchiseInformationArticles::model()->with(array('company_accounts' => $with_condition))->live()->findByPk($id, array('select' => 'id,title,text,company_id',));


public function relations()

{

  return array(

    'company_accounts' => array(self::HAS_MANY, 'CompanyAccounts', array('id' => 'company_id')),

  );

}

I can see the values from the table:


var_dump($article->company_accounts);


private '_attributes' (CActiveRecord) => 

  array

    'company' => string 'xxx' (length=3)

    'logo' => string 'xxx.jpg' (length=7)

    'logo_width' => string '150' (length=3)

    'logo_height' => string '86' (length=2)

    'id' => string '29' (length=2)

But I can’t access them like this:


$article->company_accounts->company

How can I access them ?

Did you even read my post ? Because if you would you wouldn’t have this problem anymore.

On another note, of course you can’t access like “$article->company_accounts->company” because company_accounts is NOT an object but an array of objects because you have a HAS_MANY relation therefore the need for the foreach loop.

So you are saying the join will happen simply by writing:




$article = FranchiseInformationArticles::model()->live()->findByPk($id, array('select' => 'id,title,text,company_id',))

Well… it does not.

But you are right, HAS_MANY was wrong. I needed BELONGS_TO.


public function relations()

{

  return array(

    'company_accounts' => array(self::BELONGS_TO, 'CompanyAccounts', 'company_id'),

  );

}

Now it works:


$article->company_accounts->logo

Please document yourself on how AR works in Yii, writing:




$article = FranchiseInformationArticles::model()->live()->findByPk($id, array('select' => 'id,title,text,company_id',))



will give you access to the model relations, in your case via “company_accounts” model property, it’s no mistery, it just does and that’s it.(it will do a lazy loading of course).

If it doesn’t work then simply you did something wrong.