Yii2 query for posts in a space

Question

I had a DB with a post table, a content table, and a space table.

A post is a type of content, and a space is a container for many posts. I want to get all posts within a space.

Post:

id   object_id
--------------
1    22

Content (object_id --> post.id):

id   space_id
------------------------
22   3

Space (id --> content.space_id):

id   
--------------
3

To get posts within a space, the controller function looked like this:

$posts = Post::find()
  ->joinWith('content', false)
  ->where(['{{content}}.space_id' => $space_id])
  ->all();

And the Post model had this function to get the content object for a post:

public function getContent() {
   return $this->hasOne(Content::className(), ['object_id' => 'id'])->andOnCondition(['{{content}}.object_model' => 'humhub\modules\post\models\Post']);
}

This worked perfectly until the database schema changed.

Now there is no longer a space_id column in the content table. Instead, there is a new table contentcontainer with a pk field which replaces space_id, and a class field (i.e. space class) to identify that the PK is for a space( there is also a class in the table).

The tables/relationships are now:

Post Table:

id   object_id
--------------
1    22

Content table (object_id --> post.id):

id   contentcontainer_id
------------------------
22   5

Contentcontainer Table (id --> content.contentcontainer_id)

id   pk   class
---------------
5    3    //Space

Space (id --> contentcontainer):

id   
--------------
3

To get all posts within a space, I now have to link 3 tables: post,content,contentcontainer.

Do I add the contentcontainer relationship to the Post model? Or modify the content model relationship in the Post model? Not sure how best to tackle without writing a big sloppy query.

I changed the controller function to this:

$posts = Post::find()
  ->where(['{{contentcontainer}}.pk' => $space_id])
  ->andWhere(['{{contentcontainer}}.class' => 'humhub\modules\space\models\Space'])

Not sure this is right and I am stuck setting up the contentcontainer relationship in the Post model.

Please, post the structure of the 3 tables – gmc Apr 05 '17 at 18:20 — gmc, Apr 05 '17 at 18:20
@gmc Table structures added – lilbiscuit Apr 06 '17 at 11:36 — lilbiscuit, Apr 06 '17 at 11:36

score 0 · Answer 1 · answered Apr 05 '17 at 20:29

Seems like you have a junction table - contentcontainer. There is an example in official Yii2 docs how to decalre relation via a junction table.

In your case relation in Post model could be something like this:

public function getItems()
{
    return $this->hasMany(Content::className(), ['id' => 'pk'])
        ->viaTable('contentcontainer', ['class' => 'space_id']);
}

Now you controller function will get $posts doing two joins insead of one.

gmc · Answer 2 · 2017-04-06T14:01:54.707

0

Create this method in the Space model:

public function getPosts() {
  return Post::find()
    ->innerJoin('contentcontainer', ['and', 
        'contentcontainer.pk = space.id',
        'contentcontainer.class = "humhub\modules\space\models\Space"'])
    ->innerJoin('content', 'content.contentcontainer_id = contentcontainer.id')
    ->innerJoin('post', 'post.object_id = content.id');
}

edited Apr 06 '17 at 14:01

answered Apr 06 '17 at 13:49

gmc

3,910
2
31
44

score 0 · Accepted Answer · answered Jul 09 '17 at 14:59

Here''s how I solved this (results are pulled from Content model, not Post model):

    $content = Content::find()
     ->joinWith('contentContainer')
     ->andWhere(['{{contentcontainer}}.pk' => $space_id])
     ->andWhere(['{{contentcontainer}}.class' => 'humhub\modules\space\models\Space'])
     ->andWhere(['{{content}}.object_model' => 'humhub\modules\post\models\Post'])
     ->asArray()
     ->all();

Yii2 query for posts in a space

3 Answers3