How to make a double query to the database?

Question

Yii

I have two tables: in one category and in another products.
How can I display products belonging to the desired category?


If the links were of the form: site.ru/category/id/3 - then everything is clear with this, I would receive a GET parameter, because it is in the product table.
But what about the cases with: site.ru/postelnoe-bele-gost ?
I'm confused, please help. Thank you in advance.

Answer 1

$sql = "SELECT * FROM `product_table` WHERE `category_id` IN (SELECT `id` FROM `category_table` WHERE `alias`={$alias})";

Answer 2

use the standard relation engine

Answer 3

$sql = <<<SQLSTR
SELECT `pt`.* FROM `product_table` AS `pt` 
INNER JOIN `category_table` AS `ct` ON `ct`.`category_id` = `pt`.`category_id`
WHERE `ct`.`alias` = :alias
SQLSTR;
$resultArray = Yii::$app->db->createCommand($sql)->bindValues([':alias'=>$alias])->queryAll();
$resultProductList = Product::findBySql($sql,[':alias' => $alias])->all();