Arth Arth - 1 year ago 192
MySQL Question

Yii2 eager load aggregation through junction table

I have the following tables:

  • content - id (PK), title, ... other fields ...

  • content_category - content_id (FK to content), category_id (FK to content)

Where a piece of content has_many categories, and a category is also a piece of content.

In content I have the following code:

public function getCategories()
return $this
->hasMany(Category::className(), ['id' => 'category_id'])
->viaTable('content_category', ['content_id' => 'id']);

public function getCategoriesCsv(){

For my grid view in the backend, I'd like to display a comma separated list of categories for each piece of content.

I'm aware that I could select this information separately, but I would like to do it as part of the find query and using the existing relation if possible.

Answer Source


Originally I implemented it as:

public function getCategoriesCsv(){
    $categoryTitles = [];
    foreach ($this->categories as $category){
        $categoryTitles[] = $category->title;
    return implode(', ', $categoryTitles);       

Thanks to @IStranger, I neatened this to:

public function getCategoriesCsv()
    $titles = ArrayHelper::getColumn($this->categories, 'title');
    return implode(', ', $titles);


I have now managed to avoid loading all the category models by adding a separate CategoryCsv ActiveRecord:

class CategoryCsv extends ActiveRecord
  public static function tableName(){
    return '{{%content_category}}';

  public function attributes(){
    return ['content_id', 'value'];

  public static function find(){
    return parent::find()
           ORDER BY categoryCsv.title 
           SEPARATOR ", "
         ) value'
     ->innerJoin('content categoryCsv','category_id =')

Then in the Content ActiveRecord:

public function getCategoriesCsv(){
  return $this->hasOne(CategoryCsv::className(), ['content_id' => 'id']);

Thus I can access the value like so:

$contents = Content::find()->with('categoryCsv')->all();
foreach($contents as $content){
   echo $content->categoryCsv->value;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download