0

I need to add inner join condition in layered navigation filter basic query line this:

    SELECT `e`.*,
           `cat_index`.`position` AS `cat_index_position`,
           `price_index`.`price`,
           `price_index`.`tax_class_id`,
           `price_index`.`final_price`,
           IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`,
           `price_index`.`min_price`,
           `price_index`.`max_price`,
           `price_index`.`tier_price`
    FROM `catalog_product_entity` AS `e`
    INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id
    AND cat_index.store_id=3
    AND cat_index.visibility IN (2,
                                 4)
    AND cat_index.category_id = '3'
    INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
    AND price_index.website_id = '1'
    AND price_index.customer_group_id = '4';

It is required to get only products which are exist in custom_translation in field product_id table for certain language_id, but when i add these inner join statement to query to as above like this :

    "query_as_above" INNER JOIN `custom_translation` as `t` ON t.product_id=e.entity_id
    AND t.language_id = 35;

I get empty results, althrought I have correspoding

    mysql> select language_id, product_id, name from custom_translation limit 1;
    +-------------+------------+-----------------------------------+
    | language_id | product_id | name                              |
    +-------------+------------+-----------------------------------+
    |          35 |         55 | German product virtual            |
    +-------------+------------+-----------------------------------+

and

    mysql> select * from catalog_product_entity where entity_id=55;
    +-----------+----------------+------------------+---------+----------------------------------+-------------+------------------+---------------------+---------------------+
    | entity_id | entity_type_id | attribute_set_id | type_id | sku                              | has_options | required_options | created_at          | updated_at          |
    +-----------+----------------+------------------+---------+----------------------------------+-------------+------------------+---------------------+---------------------+
    |        55 |              4 |                4 | virtual | 5a310fed97af665e5576796e69a58f8d |           1 |                0 | 2016-02-17 13:59:31 | 2016-02-18 23:36:40 |
    +-----------+----------------+------------------+---------+----------------------------------+-------------+------------------+---------------------+---------------------+

Why my statment fails and How to craft the inner join condition properly ? In result I would love the have something like:

    "layered_filter_query" INNER JOIN `custom_translation` as `t` ON t.product_id=e.entity_id
    AND t.language_id  IN (25, 36, 55);

To get all products for which there are entries in table custom_translation for certain language_id's

Thanks !

0

solved with subquery

 "query_as_above" WHERE ( SELECT e.entity_id in (SELECT translation.product_id 
                                            FROM custom_translation 
                                            AS translation 
                                            WHERE translation.language_id 
                                            IN (25, 36, 55)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.