CODEWITHSUNDEEP
mysqlsql
dazzafact
dazzafact

Reputation: 2860

Product filter by attributes

I have the following four Tables for a Product filter scenario each Product. Each product can have multiple attributes which can have multiple values.

How can i build a SQLquery for a User Request for multiple Attributes search like

CPU: >1000
Price <700

My Last Jquery was to corrupt for Performance:

SELECT
  attributes_vals.*, handys.*
FROM
  handys join handy_has_vals on handys.id=handy_has_vals.handy_id 
  join attributes_vals on attributes_vals.id=handy_has_vals.val_id 
WHERE
  `attributes_vals`.`attr_id`=374 
  AND(`attributes_vals`.`vals` >=1000 AND `attributes_vals`.`vals` >=1500) 
  OR `attributes_vals`.`attr_id`=68  AND(`attributes_vals`.`vals`='YES')
  OR `attributes_vals`.`attr_id`=11  AND( `attributes_vals`.`vals` LIKE 'GPRS%')

Fiddle & test: http://SQLFiddle.com/#!2/bba51/2

here are the Tables

handys
id  |   name    |   modell
1   |   Samsung |   galaxy A
2   |   Samsung |   galaxy B
3   |   Samsung |   galaxy C
4   |   Samsung |   galaxy D

handy_has_vals
handy_id    |   attr_id |   val_id
1           |   1       |   1
1           |   2       |   3
1           |   3       |   6
1           |   4       |   11
2           |   1       |   2
2           |   2       |   4
2           |   3       |   7
2           |   4       |   10
3           |   1       |   1
3           |   2       |   5
3           |   3       |   8
3           |   4       |   10
4           |   1       |   2
4           |   2       |   3
4           |   3       |   9
4           |   4       |   11

attributes_vals
id  |   attr_id |   vals
1   |   1       |   'YES'
2   |   1       |   'No'
3   |   2       |   '1200'
4   |   2       |   '1000'
5   |   2       |   '1500'
6   |   3       |   '300'
7   |   3       |   '350'
8   |   3       |   '800'
9   |   3       |   '550'
10  |   4       |   'YES'
11  |   4       |   'No'

attributes
id  |   attr_name
1   |   'GPS'
2   |   'CPU'
3   |   'Price'
4   |   'WLAN'

Upvotes: 2

Views: 1558

Answers (3)

Tudor Constantin
Tudor Constantin

Reputation: 26861

It should look similar to:

SELECT * FROM handys h
INNER JOIN handy_has_vals hv ON h.id = hv.handy_id
INNER JOIN attributes a ON hv.attr_id = a.id
INNER JOIN attributes_vals av ON av.attr_id = a.id
WHERE ( a.attr_name = 'CPU' AND av.vals > '1000' ) or (  a.attr_name = 'Price' AND av.vals < '700' )

And, in order to make the comparisons by numbers, not strings:

SELECT * FROM handys h
INNER JOIN handy_has_vals hv ON h.id = hv.handy_id
INNER JOIN attributes a ON hv.attr_id = a.id
INNER JOIN attributes_vals av ON av.attr_id = a.id
WHERE ( a.attr_name = 'CPU' AND  cast(av.vals AS UNSIGNED) > '1000' ) or (  a.attr_name = 'Price' AND cast(av.vals AS UNSIGNED) < '700' )

although, the usage of CAST in your where clause, guarantees you a full table scan, which will drastically decrease performance if you have plenty of records.

Upvotes: 1

Malaiyandi Murugan
Malaiyandi Murugan

Reputation: 393

try like this

SELECT 
    attributes_vals . *, handys . *
from
    handys
        join
    handy_has_vals ON handys.id = handy_has_vals.handy_id
        join
    attributes_vals ON attributes_vals.id = handy_has_vals.val_id
WHERE
    ((attributes_vals.vals >= 1000 AND attributes_vals.vals >= 1500) 
           OR attributes_vals.vals = 'YES' OR attributes_vals.vals LIKE 'GPRS%')

Upvotes: 0

Sahal
Sahal

Reputation: 4136

Try this query

SELECT 
    attributes_vals . *, handys . *
from
    handys
        join
    handy_has_vals ON handys.id = handy_has_vals.handy_id
        join
    attributes_vals ON attributes_vals.id = handy_has_vals.val_id
WHERE
    attributes_vals.attr_id IN (68, 11, 374)
    AND ((attributes_vals.vals >= 1000 AND attributes_vals.vals >= 1500) 
           OR attributes_vals.vals = 'YES' OR attributes_vals.vals LIKE 'GPRS%')

Upvotes: 0

Related Questions