CODEWITHSUNDEEP
mysqlsql
Zahirul Haque
Zahirul Haque

Reputation: 1649

Mysql data selecting with particular price range, product limit, and other filtering

I have a database table product for example:

id  name     price category
1   pen      70      1
2   banana   50      1
3   potato   10      1
5   apple    20      1
6   guaba    30      1
7   papa     30      2
8   salt     80      2
9   butter   25      2
10  cheese   10      2
11  burger   20      2
12  Mashala  25      2
13  Chuijal  40      3
14  Icecream 20      3
15  Chocolate 35     3

I want to get product from mysql database based on the price range, product limit, and category based. For example: when I want to get 4 products from category=1 on the cumulative price of those 4 products will be 100 or near to 100, it will show any 4 product from category one and the price of this 4 products will be about 100 in total. possible Output:

id  name     price category
1   pen      10      1
2   banana   50      1
3   potato   10      1
5   apple    20      1
============================
            =90 (near 100)

Again when I want to get 3 products of category 1, those prices cumulative to 100, it will show any 3 product from category 1 those prices cumulative to 100 or near 100.

Possible out put:

  id  name     price category
    1   pen      70      1
    3   potato   10      1
    5   apple    20      1
============================
                =100

Thus way, I want to get the result form different category with a different price range and product limit. Those data are, for example, actual data will be many items and many different prices and other filterings like category filtering.

Upvotes: 0

Views: 1020

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You can write a separate query for each problem to get the products:

select p1.id, p2.id, p3.id, p4.id
from products p1 join
     products p2
     on p1.id < p2.id and p1.category = 1 and p2.category = 1 join
     products p3
     on p2.id < p3.id and p3.category = 1 join
     products p4
     on p3.id < p4.id and p3.category = 1 
order by abs(100 - (p1.price + p2.price + p3.price + p4.price))
limit 1;

This is doing a brute force search, generating all the combinations of products. It should work find on your data, but if you have thousands of rows, it might take a while.

Optimizing this type of search is not a problem that can be solve wholly within a database. You can review the Wikipedia page on the knapsack problem to better understand it. The section on computational complexity essentially explains why the brute force approach is currently the best known approach for the optimization (in case you don't understand computational complexity).

Upvotes: 1

Related Questions