I have a table that stores price of products in that is hierarchically organized from country level to store level.
create table price_list(
product_id number,
country_id number,
region_id number,
store_id number,
price number
)
I need to search this table to generate invoice. The input for the function (not elaborated here) is in this pattern (i'll use json structure to explain):
{
header : {country_id:5, region_id:234, store_id:345},
items : {
0:{product_id:1001, quantity:5},
1:{product_id:299, quantity:1}
//, etc...
}
}
I want to get the price for the products specific to that store's setup.
Of course if every field is filled, I can search the price by using the complete select syntax.
select price from price_list where product_id = ? and country_id = ? and region_id = ? and store_id = ?
But there are cases where the fields in the table are incomplete, for example only the country_id and product_id is filled because the price is the same for that product in all regions and stores in that country. Let's say I don't want to populate all data with complete fields because of non-technical reasons. A sample data in the table is shown below.
product_id country_id region_id store_id price
1001 3 93 112 15
1001 3 93 113 15.5
1001 4 179 14
1001 4 185 13
1001 5 20
1001 5 999 999 25
Let's take an example if I want to execute this request
{
header : {country_id:5, region_id:300, store_id:500},
items : {
0:{product_id:1001, quantity:5},
}
}
This query will not return a result because country 5 does not specify specific price for each region and store except for region 999 store 999.
select price from price_list where product_id = 1001 and country_id = 5 and region_id = 300 and store_id = 500
Because no row is returned, I need to generalize the search, I start with using all fields, reducing one by one if no price is found.
where product_id = 1001 and country_id = 5 and region_id = 300
But still, this will not return any row. Then I generalized more, and this search gets the value.
where product_id = 1001 and country_id = 5
My current solution is exhaustive. Is there a better way to do this efficiently? The environment is Oracle 11g on php 5.4 and CodeIgniter2.
Why not create a variable which holds the where clause:
$priceWhereclause = "";
$params = [];
if(isset($productIdValue)){
$priceWhereclause .= "product_id = ? ";
$params[] = $productIdValue;
}
if(isset($countryIdValue)){
$priceWhereclause .= "and country_id= ? ";
$params[] = $countryIdValue;
}
Your query would look like:
select price from price_list where $priceWhereclause
put the $params in the execute($params) function.