Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Sequlize filtering a table in a join table

I’m trying to filter products by their attributes that are in the linking table

Product => product_has_attribute => Attribute

I was only able to do OR, but I need to make a more precise filter.

Thanks for any hint!

ps: just replace or with and – doesn’t work)

const category = await CategoryDB.findByPk(category_id);

 const products = await category.getProducts({
          offset: parseInt(offset, 10),
          limit: parseInt(limit, 10),
          where: {
            ...whereClause,
          },
          order: [["createdAt", "DESC"]],
          include: [
            {
              model: AttributesDB,
              where: {
                id: {
                  [Op.or]: [1, 2],
                },
              },
              through: {
                where: {
                  value_ua: { [Op.or]: ["Коричневий", "Середній"] },
                },
                attributes: ["value_ru", "value_ua"],
              },

              attributes: ["id", "title_ru", "title_ua", "type"],
            },
          ],
          logging: customLogger,
        });


SELECT p.*
FROM Products p
JOIN product_has_attributes pha1 ON p.id = pha1.product_id
JOIN Attributes a1 ON pha1.attribute_id = a1.id
JOIN product_has_attributes pha2 ON p.id = pha2.product_id
JOIN Attributes a2 ON pha2.attribute_id = a2.id
WHERE a1.id = 1 AND pha1.value_ua = 'Коричневий'
  AND a2.id = 2 AND pha2.value_ua = 'Середній'
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
       SELECT p.*
      FROM Products p
      JOIN product_has_category phc ON p.id = phc.product_id
      
          JOIN product_has_attributes pha1 ON p.id = pha1.product_id
          JOIN Attributes a1 ON pha1.attribute_id = a1.id
         
          JOIN product_has_attributes pha2 ON p.id = pha2.product_id
          JOIN Attributes a2 ON pha2.attribute_id = a2.id
        
      WHERE phc.category_id = 2 AND 1=1 AND (a1.id = 1 AND (pha1.value_ua = 'Коричневий' OR pha1.value_ua = 'Зелений')) AND (a2.id = 2 AND (pha2.value_ua = 'Середній')) AND p.price BETWEEN 103 AND 985

  2. 0

    It’s hard to tell without some sample data, but, if I got it right, It could be something like this:

    WITH      --  S a m p l e    D a t a :
    products AS
      ( Select 101 as ID, 'Product 101' as PRODUCT_NAME Union All 
        Select 102 as ID, 'Product 102' as PRODUCT_NAME Union All 
        Select 103 as ID, 'Product 103' as PRODUCT_NAME 
      ), 
    product_has_attributes AS
      ( Select 1010 as ID, 1 as ATTRIBUTE_ID, 101 as PRODUCT_ID, 'Brown' as VALUE_UA Union All
        Select 1011 as ID, 2 as ATTRIBUTE_ID, 101 as PRODUCT_ID, 'Average' as VALUE_UA Union All
        Select 1012 as ID, 3 as ATTRIBUTE_ID, 101 as PRODUCT_ID, 'XL' as VALUE_UA Union All
        --
        Select 1020 as ID, 1 as ATTRIBUTE_ID, 102 as PRODUCT_ID, 'Red' as VALUE_UA Union All
        Select 1021 as ID, 2 as ATTRIBUTE_ID, 102 as PRODUCT_ID, 'Average' as VALUE_UA Union All
        --
        Select 1030 as ID, 1 as ATTRIBUTE_ID, 103 as PRODUCT_ID, 'Brown' as VALUE_UA Union All
        Select 1031 as ID, 2 as ATTRIBUTE_ID, 103 as PRODUCT_ID, 'Average' as VALUE_UA 
      ),
    attributes AS
      ( Select 1 as ID, 'COLOUR' as ATTRIBUTE_NAME Union All 
        Select 2 as ID, 'CONDITION' as ATTRIBUTE_NAME Union All 
        Select 3 as ID, 'SIZE' as ATTRIBUTE_NAME 
      )

    You should put your conditions in the joins’ ON clauses in a way that your product ID is joined to both attributes of interest (1 and 2) and each attribute has a value that satisfies condition – 1-Brown, 2-Average. Try it like below:

    --      S Q L :
Select      p.*
From        products p
Inner Join  product_has_attributes pha1 ON ( p.ID = pha1.PRODUCT_ID And pha1.ATTRIBUTE_ID = 1)
Inner Join  product_has_attributes pha2 ON ( p.ID = pha2.PRODUCT_ID And pha2.ATTRIBUTE_ID = 2)
Inner Join  attributes a1 ON ( a1.ID = pha1.ATTRIBUTE_ID And pha1.VALUE_UA = 'Brown' )
Inner Join  attributes a2 ON ( a2.ID = pha2.ATTRIBUTE_ID And pha2.VALUE_UA = 'Average' )

    /*    R e s u l t :
 ID  PRODUCT_NAME
---  -----------------
101  Product 101
103  Product 103        */

    See the fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search