How in Yii2 to get the ownership of a Product from the parent of the category it refers to?

Question

Yii

Good afternoon,
There are 2 tables -
products
.id
.name
.categoryid
category (nested set)
.id
.name
.lft
.rgt
.depth
.parent_id
There is a relationship in the Products model :

public function getCategory()
    {
        return $this->hasOne(Category::className(), ['id' => 'category_id']);
    }

Those. now I can only get products of a certain category:

$model = Products::findPublished()->andWhere([
            'category_id'=>$cid
        ]);

What if, for example, my product is assigned to the category * Fake Microorganisms, And the user has selected * Microorganisms.
I want that with this choice, all products attached directly to this category and attached to child elements are displayed.

Answer 1

1. Get the values ​​of the left and right keys of the selected category using the query:

SELECT
  lft,
  rgt
FROM
  category
WHERE
  id = <id>

2. Select all categories included in the range of keys and all products included in the selected categories

SELECT
  *
FROM
  products
WHERE
  category_id IN (
    SELECT
      id
    FROM
      category
    WHERE
      lft >= <left_key>
    AND rgt <= <right_key>
  )