Marian
(Marianbucur17)
March 8, 2012, 5:17pm
1
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.
Marian
(Marianbucur17)
March 8, 2012, 7:25pm
3
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.
Marian
(Marianbucur17)
March 9, 2012, 9:36am
5
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.