Hello! I have a table, it stores data on dynastic fields. It looks like this:

 id |  product_id |  category_id |  field_id |  value_string |  value_int
 1 |  15 |  120 |  1 |  Brand 1 |  0
 1 |  16 |  120 |  1 |  Brand 2 |  0
 1 |  15 |  120 |  2 |  null |  five
 1 |  16 |  120 |  2 |  null |  15
 1 |  17 |  120 |  1 |  Brand 1 |  0
 1 |  17 |  120 |  2 |  null |  55

field_id with id 1 - brand field, with id 2 - size

I need to get a product_id whose field brand (field_id - 1) is "Brand 1" and "Brand 2" and field size (field_id - 2) with a range for example from 5 to 40

From the data above, the result should be product_id from 15 and 16. product_id from 17 does not match the range, although the brand field contains “Brand-1”

Tell me which way to go. I would like an example implementation on Yii2, but I will be grateful for a clean sql example.

PS

Maybe I did not build a table with data, if so, tell me what is wrong and how best.

Thank! All good!

    2 answers 2

    Good day. On YII2, this query might look like this.

    Model_name::find() ->select('product_id')->distinct() ->where('in', 'product_id', ['Бренд-1','Бренд-2']) ->orWhere(['between', 'product_id', 'Бренд-1','Бренд-2']) ->all();

    Details can be found here .

      Option 1

       SELECT product_id FROM `table` WHERE (field_id = 1 AND value_string IN ('Бренд-1','Бренд-2')) OR (field_id = 2 AND value_int BETWEEN 5 AND 40) GROUP BY product_id HAVING COUNT(DISTINCT field_id) = 2

      Option 2

       SELECT DISTINCT t1.product_id FROM `table` t1, `table` t2 WHERE (t1.field_id = 1 AND t1.value_string IN ('Бренд-1','Бренд-2')) AND (t2.field_id = 2 AND t2.value_int BETWEEN 5 AND 40) AND t1.product_id = t2.product_id

      Well, in general - if you took the EAV model, so read more about it ...

      Ps. How to turn it into a Yii2-code - I know FIG.

      • Increase the number of COUNT(DISTINCT field_id) for option 1. Option 2 for will require a corresponding increase in the number of copies of the table and therefore not suitable. Ps. And about EAV and standard operations on it, still read it ... then it may not be necessary to ask. - Akina