How to specify an ORDER BY from comparisons and null checks in MySQL?

Question

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

Working Example

This is _not_ a complete question because you showed us no data directly, nor did you give any background on the problem. Voting to close as unclear. — Tim Biegeleisen, Jul 01 '20 at 08:03
@Nick 1st show products with price differents then products with name_new where is null then products with product_code where is null — Ivan, Jul 01 '20 at 08:05
@TimBiegeleisen what do you mean with ? you showed us no data directly — Ivan, Jul 01 '20 at 08:07
level 3 is the last level of order. Why don't you want it placed at the bottom of the results? — forpas, Jul 01 '20 at 08:09
@forpas because level 3 mean its a new product should be place after product with price diff (level 1) and missing/deleted (level 2) products — Ivan, Jul 01 '20 at 08:10
I do not see any reason to perform this task using excess external tools (cron and PHP) - it can be easily solved in stored procedure form executed by MySQL Event Scheduler. If you need to see the rows which were updated during the process you may store the info about this in separate log table which will be shown by PHP code and truncated manually by the client request and/or automatically (for example, by viewing fact, or by stored procedure before updationg process). — Akina, Jul 03 '20 at 09:39
@Akina the idea is good but show will i load xml and export what ever i need without PHP ? — Ivan, Jul 03 '20 at 14:34
[MySQL 8.0 Reference Manual / ... / LOAD XML Statement](https://dev.mysql.com/doc/refman/8.0/en/load-xml.html) — Akina, Jul 03 '20 at 15:41

score 3 · Accepted Answer · answered Jul 03 '20 at 11:36

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.

For a second level of sorting such as by `name`: `ORDER BY CASE ... END, name` — Rick James, Jul 03 '20 at 16:56

score 0 · Answer 2 · answered Jul 05 '20 at 01:59

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

How to specify an ORDER BY from comparisons and null checks in MySQL?

2 Answers2