Compare a group of products across different suppliers - SQL vs PHP

Question

I have a form which allows user to choose a group of products and compare the prices across different suppliers

This is the data looks like:

Suppliers table

Suppliers (id, name)
Values (1, 'Shop 1')
Values (2, 'Shop 2')
Values (3, 'Shop 3')
...

Devices table

Devices (id, name)
Values (1, 'IBM x201')
Values (2, 'IBM x220')
Values (3, 'IBM x230')
...

Products table

Products (id, device_id, supplier_id, hdd_size, cpu, price)
Values (1, 1, 1, 160, 2.0, 300)
Values (2, 1, 1, 320, 2.0, 330)
Values (3, 1, 1, 160, 2.2, 360)
Values (4, 1, 1, 320, 2.2, 410)
Values (5, 1, 2, 160, 2.0, 310)
Values (6, 1, 2, 320, 2.0, 340)
Values (7, 1, 2, 160, 2.2, 370)
Values (8, 1, 2, 320, 2.2, 420)
...

So user might request prices for q or more devices giving different crytria:

Request:

[ 
   { device_id: 1, hdd: 160 },
   { device_id: 3, cpu: 2.0}
]

So the user wants to buy IBM x201 with hdd of at least 160GB and IBM x230 with cpu of at least 2.0GHZ. This should return prices grouped by a supplier_id, for example

{ supplier_id: 1, price: 730, products: [
    { id: 1, hdd_size: 160, cpu: 2.0, price: 300 },
    { id: 22, hdd_size: 160, cpu: 2.2, price: 430 },
]},
{ supplier_id: 1, price: 780, products: [
    { id: 1, hdd_size: 160, cpu: 2.0, price: 300 },
    { id: 37, hdd_size: 320, cpu: 2.2, price: 480 },
]},
{ supplier_id: 2, price: 750, products: [
    { id: 12, hdd_size: 160, cpu: 2.0, price: 310 },
    { id: 39, hdd_size: 160, cpu: 2.0, price: 440 },
]},
...

How to do it in SQL. I'm trying different ways but I'm getting to a point where I think it might be actually easier to do the grouping and calculations outside of DB.

Any thoughts?

EDIT: http://sqlfiddle.com/#!2/162b62

Answer 1

Assuming you need the 2 lowest valued products satisfying the conditions for each supplier, following is the solution

select supplier_id, sum(price)as sum, array[ array[(select id from products where supplier_id=P.supplier_id order by price asc limit 1), (select hdd_size from products where supplier_id=P.supplier_id order by price asc limit 1), (select cpu from products where supplier_id=P.supplier_id order by price asc limit 1), (select price from products where supplier_id=P.supplier_id order by price asc limit 1)] , array[(select id from products where supplier_id=P.supplier_id order by price asc offset 1 limit 1), (select hdd_size from products where supplier_id=P.supplier_id order by price asc offset 1 limit 1), (select cpu from products where supplier_id=P.supplier_id order by price asc offset 1 limit 1), (select price from products where supplier_id=P.supplier_id order by price asc offset 1 limit 1)] ] from products as P where device_id=(select id from devices where name ilike 'ibm x201') and cpu>=2.0 and hdd_size>= 160 group by supplier_id having count(id)>2 ;

You can easily modify this to get multiple counts of products by creating the sql statement in a for loop in the language you are intending to use it in. It is lengthy and I really don't know about the performance, but it does the job.

It does fare well than getting the count for all the suppliers individually (tested for 2 suppliers). I am not sure how it will fare when the tuple count increases.

Answer 2

Try

SELECT 
name, device_id, supplier_id, hdd, cpu, price
FROM 
products p JOIN
devices d ON p.device_id = d.id
WHERE 
(name = 'ibm x201' AND hdd >= 160)
OR
(name = 'ibm x230' AND cpu >= 2.0)

SQLFIDDLE