CODEWITHSUNDEEP
phpyii2
Web Worker
Web Worker

Reputation: 51

Yii2: How to get records which its attribute is in an array?

I built a website with Yii2 and I tried to get the records which their categories are available. I have got the available categories and stored them in an array. But it returns nothing when I tried to use the 'in' condition for the next query. I am sure that the database records are correct.

// Get active categories
$active_cat = Category::find()
              ->where(['status' => SystemCode::STATUS_ACTIVE])
              ->andwhere(['is_deleted' => 0])
              ->select(['id'])
              ->asArray()
              ->all();

// Get available articles
$query = Articles::find()
         ->where(['in', 'cat_id', $active_cat]);

Upvotes: 3

Views: 6327

Answers (4)

rob006
rob006

Reputation: 22174

You're using all() to get list of categories, which will give you array of rows in format:

[
    ['id' => 1],
    // ...
],

But for your next query you need a flat list of IDs - you can achieve this by using column() method instead:

// Get active categories
$active_cat = Category::find()
              ->where(['status' => SystemCode::STATUS_ACTIVE])
              ->andWhere(['is_deleted' => 0])
              ->select(['id'])
              ->column();

// Get available articles
$query = Articles::find()
         ->where(['in', 'cat_id', $active_cat]);

Upvotes: 3

user206
user206

Reputation: 1105

Please note the return value of each method:

  • one() This method returns an ActiveRecord object or null if not found.

    one() Executes query and returns a single row of result.

  • all() This method returns a list of ActiveRecord objects or an empty array if not found

    all() Executes the query and returns all results as an array. So in the all() method: 

    foreach($active_cat as $cat){
   # code...
}

    Here, of course, you can access the second table by "Relational Data"

asArray() Whether to return the query results in terms of arrays instead of Active Records.

Also:

yii\db\ActiveRecord::findOne(): returns a single Active Record instance populated with the first row of the query result. (Returns a single active record model instance by a primary key or an array of column value)

yii\db\ActiveRecord::findAll(): returns an array of Active Record instances populated with all query result. (a list of active record models that match the specified primary key value(s) or a set of column values.)

Upvotes: 0

Raul Sauco
Raul Sauco

Reputation: 2705

Assuming that you have a one-to-many relation between Articles and Category defined as follows:

/**
 * @return \yii\db\ActiveQuery
 */
public function getCategory()
{
    return $this->hasOne(Category::class, ['id' => 'cat_id']);
}

You can get all Articles records where status = SYSTEM_CODE::STATUS_ACTIVE like this:

$articles = $query = Articles::find()->joinWith('category')
    ->where([
        'category.status' => SYSTEM_CODE::STATUS_ACTIVE,
        'category.is_deleted' => 0
    ])->all();

If, instead the relation is many-to-many, and it is defined like this:

/**
 * @return \yii\db\ActiveQuery
 */
public function getCategories()
{
   return $this->hasMany(
        Category::className(), 
        ['id' => 'cat_id'])->viaTable(
          'article_category', ['article_id' => 'id']
        );
}

Then you can get the same result like this: 

$articles = $query = Articles::find()->joinWith('categories')
    ->where([
        'category.status' => SYSTEM_CODE::STATUS_ACTIVE,
        'category.is_deleted' => 0
    ])->all();

Only the name of the relation changes in the second example.

Note that I have made a few assumptions, like the name of the category table being category, a junction table being called article_category and so on, you may have to modify the examples to fit your code.

The documentation for joinWith().

Upvotes: 0

Serghei Leonenco
Serghei Leonenco

Reputation: 3507

Here is how you can do: You have to reorganize your code: in Categories model you have to put this function:

public function getActiveArticles()
{
    return $this=->hasMany(Articles::classname(), ['cat_id' => 'id'])->andWhere(['categories_table.status' => SystemCode::STATUS_ACTIVE, 'categories_table.is_deleted' => 0]);
}

Now you can access all articles by:

$active_cat = Category::find()->all();
foreach($active_cat as $cat)
{
    if($cat->activeAcrticles !== null)
    {
        foreach($cat->activeAcrticles as $article)
        {
            echo $article->name;
        }
    }
}

Upvotes: -1

Related Questions