I have two tables feed_old(default data) and feed_new(new data) where cron job will run every day and update ONLY feed_new table with the current information
Cron job
$url = "localhost/test.xml";
$xml = simplexml_load_file($url);
$this->db->query("TRUNCATE TABLE feed_new");
$date = date('Y-m-d H:i:s');
foreach($xml->Product as $item)
{
$data = array(
'product_code' => $item->ProductCode,
'name' => $item->Name,
'price' => $item->RetailCurrentPrice,
'stock' => (int)$item->Stock,
'manufacture' => '1',
'date_updated' => $date
);
$update_status = $this->model_price->check_price($data);
}
Model
public function check_price($data)
{
if($data) {
$insert = $this->db->insert('feed_new', $data);
return ($insert == true) ? true : false;
}
}
Since here everything working fine
Problem is coming when i have compare feed_new vs feed_old and get what ever changes are and display all records
After comparing feed_new vs feed_old i will like to pull all records from both tables and order them by levels
Level 1 - If products have different price feed_new.price <> feed_old.price
Level 2 - If product from feed_old is not exist in feed_new (that mean product is not supported anymore)
Level 3 - If product from feed_new is not exist in feed_old (that mean product is new)
Level 4 - rest of results
Compare query
SELECT fn.name AS name_new, fo.date_updated, fo.id, fo.name,fo.price,fo.product_code, fn.product_code AS product_code_new, fo.stock, fn.price AS price_new, fn.stock AS stock_new, fn.date_updated AS date_updated_new
FROM feed_old fo
LEFT JOIN feed_new fn ON fo.product_code = fn.product_code
UNION ALL
SELECT fn.name AS name_new, fo.date_updated, fo.id, fo.name,fo.price,fo.product_code, fn.product_code AS product_code_new, fo.stock, fn.price AS price_new, fn.stock AS stock_new, fn.date_updated AS date_updated_new
FROM feed_old fo
RIGHT JOIN feed_new fn ON fn.product_code = fo.product_code
WHERE fo.product_code IS NULL OR fn.name IS NULL ORDER BY COALESCE(price <> price_new, name_new is NULL, product_code IS NULL) DESC
Problem is that Level - 3 is always shown at last record i mean after level - 4 in my working example below you can see name_new -> test3 is of the bottom of the table when should be on 3rd position
How can i order them by levels above
Santiago Trujillo
COALESCE doesn't seem like the most appropriate function for this ordering as it just returns the first non-NULL value, which isn't intuitive when doing comparisons where the operands could be NULL.
Would suggest using CASE instead with a dummy integer value for level - something like this:
ORDER BY CASE WHEN price_new IS NOT NULL AND price_new <> price THEN 1
WHEN price_new IS NULL THEN 2
WHEN price IS NULL THEN 3
ELSE 4
END
See this SQLFiddle demo: http://sqlfiddle.com/#!9/57c8eb3/6.
You can write a better solution using IFNULL, CASE WHEN and UNION.
Solution - http://sqlfiddle.com/#!9/57c8eb3/21
SELECT fn.name AS name_new, fo.date_updated, fo.id, fo.name,fo.price,
fo.product_code, fn.product_code AS product_code_new, fo.stock, fn.price AS price_new,
fn.stock AS stock_new, fn.date_updated AS date_updated_new,
(case when (IFNULL(fn.product_code,'X') != 'X' and fo.price != fn.price)
then 'Level1'
when IFNULL(fn.product_code,'X') = 'X' then 'Level2'
else 'Level4' end) as Ordering_level
FROM feed_old fo LEFT JOIN feed_new fn
ON fo.product_code = fn.product_code
UNION
SELECT fn.name AS name_new, fo.date_updated, fo.id, fo.name,fo.price,
fo.product_code, fn.product_code AS product_code_new, fo.stock,
fn.price AS price_new, fn.stock AS stock_new, fn.date_updated AS date_updated_new,
(case when (IFNULL(fo.product_code,'X') != 'X' and fo.price != fn.price)
then 'Level1'
when IFNULL(fo.product_code,'X') = 'X' then 'Level3'
else 'Level4' end) as Ordering_level
FROM feed_old fo RIGHT JOIN feed_new fn
ON fn.product_code = fo.product_code
order by ordering_level