CODEWITHSUNDEEP
mysqlsqlselectsql-order-bycoalesce
Ivan
Ivan

Reputation: 433

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

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

Upvotes: 5

Views: 189

Answers (2)

Shantanu Kher
Shantanu Kher

Reputation: 1054

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

Upvotes: 0

Steve Chambers
Steve Chambers

Reputation: 39424

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.

Upvotes: 3

Related Questions