Yii2 Select data from two tables

Question

how I can choose columns from relation table ?

$orders=Category::find()
->joinWith('posts')
->all();

I tried yet

$orders=Category::find()
->select('post.*')
->joinWith('posts')
->all() 

etc, but i gets error: Getting unknown property: common\models\Category::id_post

Excecute Sql statment : SELECT `post`.* FROM `category` LEFT JOIN `post` ON `category`.`id_category` = `post`.`id_categoryFK`

but i can't using columns from table post I would like to write data from the joined tables

Answer 1

There is a really good explanation in this Yii2 Wiki Article. But I will simplify that article, using your input.

You probably have two models that you want to join: common\models\Category and common\models\Post with all the attributes matching the ones in the Database. Also, let's say that Category has many Posts associated to it, but a Post only has one Category.

1. Define the relations in the Models

First you have to make Yii2 to understand that there is a relation between two Models. Yii2 will do some magic to help you join the tables, but you have to setup the models fist.

1.1 Editing Category Model

In common\models\Category add the following lines:

/**
 * @return \yii\db\ActiveQuery
 */
public function getPosts()
{
    return $this->hasMany(Posts::className(), ['category_id' => 'id_categoryFK']);
}

1.2 Editing Post Model

In common\models\Post add the following lines:

/**
 * @return \yii\db\ActiveQuery
 */
public function getCategory()
{
    return $this->hasOne(Post::className(), ['id_categoryFK' => 'category_id']);
}

2. Retrieve the data

Now, from any controller you can use it like this:

/* Access posts from a category*/
$category = common\models\Category::find()->where(['category_id'=>$category_id])->one();
foreach($category->posts as $post) // iterate over all associated posts of category
{
    $postTitle = $post->title; //access data of Post model
}

/* Access category from post */
$post = common\models\Post::find()->where(['id'=>$post_id])->one();
$categoryName = $post->category->category_name;

3. Keep learning

As I said in the beginning, this Yii2 Wiki Article will help you in most of your cases. This other Yii2 official guide, is very good too.

Happy coding!