Opencart2 How to get product filter by category and brand?

Question

opencart

Good afternoon!
Please tell me how to generate a false JSON query to the opencart2 (mysql) database to get
All filters (standard) by category and manufacturer.
As a result, they should have an array of filter groups (with their values) for products in the current category, filtered by manufacturer, for further processing by controllers.

Answer 1

I figured it out myself!
Returns filter groups by category and brand

$category_id = 1;
$manufacturer_id = 5; 
$query = $this->db->query("SELECT DISTINCT f.name, f.filter_group_id FROM ".DB_PREFIX."filter_group_description f
LEFT JOIN ".DB_PREFIX."filter l ON(f.filter_group_id = l.filter_group_id)
LEFT JOIN ".DB_PREFIX."product_filter t ON(l.filter_id = t.filter_id)
LEFT JOIN ".DB_PREFIX."product p ON(t.product_id = p.product_id)
LEFT JOIN ".DB_PREFIX."product_to_category c ON(p.product_id = c.product_id)
WHERE c.category_id = '".(int)$category_id."' AND p.manufacturer_id = '".(int)$manufacturer_id."'");

Returns filters By category, brand and filter group

$category_id = 1;
$manufacturer_id = 5; 
$filter_group_id;

$query2 = $this->db->query("SELECT DISTINCT l.filter_id, l.filter_group_id, l.name FROM ".DB_PREFIX."filter_description l
LEFT JOIN ".DB_PREFIX."product_filter t ON(l.filter_id = t.filter_id)
LEFT JOIN ".DB_PREFIX."product p ON(t.product_id = p.product_id)
LEFT JOIN ".DB_PREFIX."product_to_category c ON(p.product_id = c.product_id)
WHERE l.filter_group_id = '" . $filter_group_id ."' AND c.category_id = '".(int)$category_id."' AND p.manufacturer_id = '".(int)$manufacturer_id."'");

But these are just examples, I used their options in different places for a specific task.
If anyone knows a better option, please let me know!