I have an application that take the user location, check which store sell on that location and then calculate the total price for the products the user selected on the shopping cart. For example, if the user selected:
Product | Amount
Shoes | 1
T-Shirt | 3
Pants | 2
Hat | 1
Gloves | 2
Then I need to check every store that sell on the users location and how much would it cost for the user to buy all the products. This is what I have so far:
/**
* @POST
* Variables received via POST method
* $cart: Shopping cart with the products user selected;
* $location: Location Object with the user address;
*/
// Getting the information needed to start the query
$store_list = getStoreList($location); // Another function to return an array with each store ID that sell on the user location
$product_list = array_column($cart->products, 'id_product');
$product_total = count($cart->products); // Total amount of unique items on the ShoppingCart. In this example, would be 5 (not the sum of quantity)
// Select the list of products and the URL reference for the store
$query = Sql("SELECT
a.price, a.id_product,
b.url
FROM
tb_product_store a,
tb_store b
WHERE a.id_store = b.id
AND a.id_store IN (".implode(',', array_map('intval', $store_list)).")
AND a.id_product IN (".implode(',', array_map('intval', $product_list)).")
");
/**
* Now I need to group the product list for each Store URL instead of
* having all the products, no matter which store it belongs to in a
* single array.
*
* turns this:
* array(
* {id_produtct: 1, price: 4.99, url: 'store-a'},
* {id_produtct: 2, price: 6.99, url: 'store-a'},
* {id_produtct: 1, price: 4.99, url: 'store-b'},
* {id_produtct: 2, price: 6.99, url: 'store-b'}
* );
*
* into this:
* array(
* 'store-a': array(
* {id_produtct: 1, price: 4.99, url: 'store-a'},
* {id_produtct: 2, price: 6.99, url: 'store-a'}
* ),
* 'store-b': array(
* {id_produtct: 1, price: 4.99, url: 'store-b'},
* {id_produtct: 2, price: 6.99, url: 'store-b'}
* )
* )
*/
function group_by($array, $key) {
$return = array();
foreach($array as $val) {
$return[$val[$key]][] = $val;
}
return $return;
}
$newProductList = group_by($query, 'url');
/**
* Now I do all the calculations on these lists to get:
* total_price: Total sum of the product list (price * quantity)
* total_item: Total unique items on the list
*/
$stores = array();
foreach ($newProductList as &$row) {
$obj = new StdClass();
$obj->total_price = 0;
$obj->total_item = 0;
foreach ($row as &$rowS) {
$obj->url = $rowS['url'];
foreach ($cart->products as $rowP) {
if (intval($rowS['id_product']) === intval($rowP['id_product'])) {
$rowS['quantity'] = $rowP['quantity'];
$rowS['price'] = $rowS['price'] * $rowS['quantity'];
$obj->total_price += $rowS['price'];
$obj->total_item++;
}
}
}
array_push($stores, $obj);
}
/**
* Now I create 2 arrays and fill them with just the necessary information
* to display on the view for the user to select which store he wants to
* buy from.
*
* These arrays are:
* list_complete: A list of the stores that has all the items selected on the shopping cart
* list_incomplete: A list of the stores that DOESN'T have all the items selected
*/
$list = array(
'list_complete' => array(),
'list_incomplete' => array(),
);
foreach($stores as &$row) {
$total_price = $row->total_price;
$total_item = intval($row->total_item);
// Load all the other information for each Store (such as name, logo, address, etc..) based on the Store URL
$obj = getStoreDetail($row->url);
$obj->total_price = $total_price;
$obj->total_item = $total_item;
// Check if the store has the correct amount of items
if ($total_item === $product_total) {
array_push($list['list_complete'], $obj);
} else if ($total_item < $product_total) {
$obj->missing_items = $product_total - $total_item;
array_push($list['list_incomplete'], $obj);
}
}
// Now I just sort the arrays based on total price and amount of items
// Sort complete
array_multisort(
array_column($list['list_complete'], 'total_price'), SORT_ASC,
$list['list_complete']
);
// Sort incomplete
array_multisort(
array_column($list['list_incomplete'], 'total_item'), SORT_DESC,
array_column($list['list_incomplete'], 'total_price'), SORT_ASC,
$list['list_incomplete']
);
// And finally I return the list of stores
return $list;
What I find the most difficult is the fact that I need to take in consideration the amount of items selected on the shopping cart.
If it was only 1 item for each product, I could do something like this:
$query = Sql::load("SELECT
SUM(a.price) as 'total_price',
COUNT(a.id_product) as 'total_item',
b.url
FROM
tb_product_store a,
tb_store b
WHERE a.id_store = b.id
AND a.id_store IN (".implode(',', array_map('intval', $store_list)).")
AND a.id_product IN (".implode(',', array_map('intval', $product_list)).")
GROUP BY b.url
ORDER BY total_item, total_price
");
Is there a way I can improve this code taking these requirements in consideration? Performance is always a concern, since this can have many stores on the list, but it's more important that it works properly instead of faster.
