We have a self referencing product-to-product relationship, and I am struggling to return data based on another FK in the pivot table.
I'm playing with a sample pivot table that looks like this:
tmp_prod2prod:
And that affiliation_type_id FK refers to this table:
affiliation_types:
(BTW, if you want to see a full schema, find it here on pastebin)
GOAL:
My goal is to show affiliation_types.type.
My model looks like:
class TmpProd extends Eloquent {
protected $table = 'tmp_prods';
public $timestamps = false;
public function affiliatedToProducts()
{
return $this->belongsToMany('TmpProd', 'tmp_prod2prod', 'p_id_to', 'p_id_from')
->withPivot('affiliation_type_id')
->join('affiliation_types', 'tmp_prod2prod.affiliation_type_id', '=', 'affiliation_types.id');
}
public function affiliatedFromProducts()
{
return $this->belongsToMany('TmpProd', 'tmp_prod2prod', 'p_id_from', 'p_id_to');
}
}
and my controller I'm running:
$model = TmpProd::find(1);
foreach ($model->affiliatedToProducts as $cross) {
echo"<pre>",var_dump($cross->pivot),"</pre>\n";
}
I've tried enough variations that I feel like my head is spinning :)
How do I reconcile the affiliation_types.type based on the affiliation_type_id FK in the pivot table?
After going round and round with custom pivot models, all sorts of convoluted relations, etc., I believe I've found a simple solution—just add a select statement to your relation, targeting the affiliation_types table, so that you have access to the types column:
return $this->belongsToMany('TmpProd', 'tmp_prod2prod', 'p_id_to', 'p_id_from')
->withPivot('affiliation_type_id')
->join('affiliation_types', 'tmp_prod2prod.affiliation_type_id', '=', 'affiliation_types.id')
->addSelect('affiliation_types.type as affiliation_type');
(The as affiliation_type part is optional, but will make your query clearer semantically.)
Then, in your foreach loop, you can call:
var_dump($cross->affiliation_type);
It would also be a good idea to eager-load your affiliatedToProducts relations, to reduce the number of queries from N+1 down to 2. So, to find a particular product by ID:
$model = TmpProd::with('affiliatedToProducts')->find(1);
Or, if you need to retrieve all products:
$model = TmpProd::with('affiliatedToProducts')->get();
...then loop over the results with foreach ($model as $m).
Update: This worked for Laraval v4.2.8, but as of v4.2.11, it seems (counterintuitively) that ->addSelect replaces, rather than adds, the query's original select statement, and is functionally identical to just using ->select. So for versions newer than 4.2.8, it is necessary to add the original table back into the query, so the belongsToMany relation should end with:
->select('affiliation_types.type', 'tmp_prods.*');
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加