1

OneToMany relationship

I am not a pro with mysql so I guess there is some simple solution for my problem. Basically:

how can check each row individually if it contains a certain values (if two columns in a row contains two custom values).

I know how I could check the first row thanks to GROUP BY ran on unique index of config__atribute__value table. But what if I want to make another AND to check if some other row also matches different values.

Query abstract representation:

SELECT price
FROM one
LEFT JOIN many
    ON one.id = many.fk_one
WHERE (col1 = "some_value" AND col2 = "some_value") AND (col1 = "some_value" AND col2 = "some_value")
GROUP BY one.id

* Both rows have to mach

(ONE) table product_config 

id, product_type, inStock, inSale, price
1   "flyers A4"   1        1       800.00

(MANY) table config__atribute__value

id | fk_product_config | fk_product_atribute | fk_product_atribute_value
7    1                   "color"               "4/0"
8    1                   "paper"               "120g"

Original query:

tables

The query below works for one row only. But how can I check the next row ?

SELECT price
FROM product_config p
LEFT JOIN config__atribute__value c
    ON p.id = c.fk_product_config
WHERE (c.fk_product_atribute = "paper" AND c.fk_product_atribute_value = "120g")
GROUP BY p.id

This will not match anything because it is checking each row for all the values.

SELECT price
FROM product_config p
LEFT JOIN config__atribute__value c
    ON p.id = c.fk_product_config
WHERE (c.fk_product_atribute = "paper" AND c.fk_product_atribute_value = "120g") AND (c.fk_product_atribute = "color" AND c.fk_product_atribute_value = "4/0")
GROUP BY p.id

Ideally, if I could use ORDER BY for each row, it would solve my problem, of course this would throw an Error.

SELECT price
FROM product_config p
LEFT JOIN config__atribute__value c
    ON p.id = c.fk_product_config
WHERE (c.fk_product_atribute = "paper" AND c.fk_product_atribute_value = "120g")
GROUP BY p.id AND (c.fk_product_atribute = "color" AND c.fk_product_atribute_value = "4/0")
GROUP BY p.id

Error

I am adding this so you can better understand what I am trying to do.

Antoine
  • 800
  • 3
  • 14
  • 29
DevWL
  • 17,345
  • 6
  • 90
  • 86
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Oct 01 '17 at 15:45

2 Answers2

1

Let INNER JOIN do the work for you!

SELECT price FROM product_config p 
INNER JOIN config__atribute__value c1 ON p.id = c1.fk_product_config 
  AND c1.fk_product_atribute = 'paper' 
  AND c1.fk_product_atribute_value = '120g'
INNER JOIN config__atribute__value c2 ON p.id = c2.fk_product_config
  AND c2.fk_product_atribute = 'color' 
  AND c2.fk_product_atribute_value = '4/0'

Because of using inner join, the only product configs returned will be those that have both attributes, and those attributes have the desired values.

This might demonstrate why EAV is a terrible way to structure relational data. Your queries become huge and complicated.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank You Bill. This works great!. I see that you have something more to say about EAV, tell me please what aproach would you reccomand in case like mine if not EAV. – DevWL Oct 01 '17 at 19:49
  • 1
    https://stackoverflow.com/questions/695752/how-to-design-a-product-table-for-many-kinds-of-product-where-each-product-has-m/695860#695860 – Bill Karwin Oct 01 '17 at 19:49
0

Have you tried using OR instead of AND?

WHERE 
(c.fk_product_atribute = "paper" and c.fk_product_atribute_value = "120g") 
OR 
(c.fk_product_atribute = "color" and c.fk_product_atribute_value = "4/0")
beautiful.drifter
  • 311
  • 1
  • 7
  • Perhaps I was not clear enough about what I am trying to achive. Both rows have to mach. – DevWL Oct 01 '17 at 08:33