Filter on relational data (foreign keys?) not working

Hello community,

I’m struggling to filter my rest collections.

This is my simple data model:

I want to filter the user_has_product REST endpoint based on a category.

I can use filters on the corresponding REST endpoint to filter for users for example (rating work as well):

/web/user-has-products?filter[user_id]=1

<response>
<item>
<user_id>1</user_id>
<product_id>1</product_id>
<product_brand_id>2</product_brand_id>
<product_category_id>1</product_category_id>
<rating_id>1</rating_id>
</item>
<item>
<user_id>1</user_id>
<product_id>3</product_id>
<product_brand_id>2</product_brand_id>
<product_category_id>1</product_category_id>
<rating_id>2</rating_id>
</item>
<item>
<user_id>1</user_id>
<product_id>4</product_id>
<product_brand_id>3</product_brand_id>
<product_category_id>1</product_category_id>
<rating_id>3</rating_id>
</item>
</response>

When I try to filter by the category (product_category_id) it will show me that the filter is invalid.

/web/user-has-products?filter[product_category_id]=1

<response>
<item>
<field>filter</field>
<message>Product Category ID is invalid.</message>
</item>
</response>

I can also not filter for the product_id, same message.

I tried adding the category via extrafields (which works) and then use the category.id to filter, but that gives me the following message:

/web/user-has-products?expand=category&filter[category.id]=5

<response>
<item>
<field>filter</field>
<message>Unknown filter attribute "category.id"</message>
</item>
</response>

And this is how the UserHasProductsController looks like:

<?php

namespace app\controllers;

use yii\rest\ActiveController;

class UserHasProductController extends ActiveController
{
    public $modelClass = 'app\models\UserHasProduct';

    public function actions()
    {
        $actions = parent::actions();
        $actions['index']['dataFilter'] = [
            'class' => \yii\data\ActiveDataFilter::class,
            'searchModel' => $this->modelClass,
        ];
        return $actions;
    }

}

Help would be really appreciated!

I also don’t understand why rating_id and user_id can be used for filtering, but product_id not.

Attributes that should be used for filtering must be defined in the validation rules of the searchModel, at least with the safe rule as the bare minimum.

Check RESTful Web Services: Filtering Collections | The Definitive Guide to Yii 2.0 | Yii PHP Framework

1 Like

Thank you for your response!

In the UserHasProduct model the attributes are defined in the rules method:

    public function rules()
    {
        return [
            [['user_id', 'product_id', 'product_brand_id', 'product_category_id'], 'required'],
            [['user_id', 'product_id', 'product_brand_id', 'product_category_id', 'rating_id'], 'integer'],
            [['user_id', 'product_id', 'product_brand_id', 'product_category_id'], 'unique', 'targetAttribute' => ['user_id', 'product_id', 'product_brand_id', 'product_category_id']],
            [['product_id', 'product_brand_id', 'product_category_id'], 'exist', 'skipOnError' => true, 'targetClass' => Product::class, 'targetAttribute' => ['product_id' => 'id', 'product_brand_id' => 'brand_id', 'product_category_id' => 'category_id']],
            [['rating_id'], 'exist', 'skipOnError' => true, 'targetClass' => Rating::class, 'targetAttribute' => ['rating_id' => 'id']],
            [['user_id'], 'exist', 'skipOnError' => true, 'targetClass' => User::class, 'targetAttribute' => ['user_id' => 'id']],
        ];
    }