Arth Arth - 1 month ago 13
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

WITH CATEGORIES LOADED

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);
}

WITHOUT CATEGORIES LOADED

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()
      ->select([
        'content_id',
        'GROUP_CONCAT(
           categoryCsv.title 
           ORDER BY categoryCsv.title 
           SEPARATOR ", "
         ) value'
       ])
     ->innerJoin('content categoryCsv','category_id = categoryCsv.id')
     ->groupBy('content_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;
}
Comments