How to rewrite SQL-query with CASE/WHEN to Joomla-based "format"

Question

I have the following SQL-query to database:

$jinput = JFactory::getApplication()->input;
$category_id = $jinput->get('virtuemart_category_id');
$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query = 'SELECT b.`virtuemart_product_id`,
    (CASE 
        WHEN b.`product_override_price` > 0 THEN b.`product_override_price`
        WHEN b.`product_discount_id` > 0 THEN  b.`product_price` - (b.`product_discount_id` * b.`product_price` / 100)
        WHEN b.`product_price` < 1 THEN 1
        ELSE b.`product_price` 
        END) as final_price
    FROM #__virtuemart_product_categories as a
    LEFT JOIN #__virtuemart_product_prices as b ON b.`virtuemart_product_id` = a.`virtuemart_product_id`
    AND `virtuemart_category_id` = '.$category_id.'
    ORDER BY final_price ASC';
$db->setQuery($query);
$rows = $db->loadObjectList();

How to rewrite the lines with CASE, WHEN ... THEN ... to Joomla-based "format" with filtering like:

$query->select('a.virtuemart_product_id');
$query->from('#__virtuemart_product_categories AS a');
$query->where($db->quoteName('virtuemart_category_id')." = ".$db->quote($category_id));
... // ???
$query->join('LEFT', '#__virtuemart_...');
$query->order('final_price ASC');

Answer 1

I'll recommend this syntax:

$db = JFactory::getDBO();
try {
    $query = $db->getQuery(true)
                ->select(
                    array(
                        $db->qn('b.virtuemart_product_id'),
                        "CASE WHEN " . $db->qn('b.product_override_price') . " > 0" .
                            " THEN " . $db->qn('b.product_override_price') .
                            " WHEN " . $db->qn('b.product_discount_id') . " > 0" .
                            " THEN " . $db->qn('b.product_price') . " - (" . $db->qn('b.product_discount_id') . " * " . $db->qn('b.product_price') . " / 100)" .
                            " WHEN " . $db->qn('b.product_price') . " < 1" .
                            " THEN 1" .
                            " ELSE " . $db->qn('b.product_price') .
                            " END AS " . $db->qn('final_price')
                    )
                )
                ->from($db->qn('#__virtuemart_product_categories', 'a'))
                ->innerJoin($db->qn('#__virtuemart_product_prices', 'b') . " ON " . $db->qn('b.virtuemart_product_id') . " = " . $db->qn('a.virtuemart_product_id'))
                ->where($db->qn('a.virtuemart_category_id') . " = " . (int)$category_id)
                ->order($db->qn('final_price'));
    echo $query->dump();    // of course, don't do this on your live/public site
    $db->setQuery($query);
    echo "<pre>";
        var_export($db->loadObjectList());
    echo "</pre>";
} catch (Exception $e) {
    echo "<div>", $e->getMessage(), "</div>";  // of course, don't do this on your live/public site
}

• qn() is the alias for quoteName() and is used to backtick-wrap tablenames, columnnames, and their aliases. When assigning a table alias, just write it as the 2nd parameter in qn()
• I altered your query structure slightly and wrote your $category_id into a WHERE clause -- because I believe it to be more appropriate and easier to read.
• I prefer to chain methods all the way from getQuery() -- because it can be done.
• Visually, the CASE syntax is rough on the eyes, I've tried to improve readability by lining up the keywords, but other developers may prefer a different style.
• (int) is applied to $category_id as a security precaution.
• The try-catch() blocks are handy when debugging, just don't ever display queries or errors on your live site.

With a category_id of 1, the rendered query looks like this: (I tabbed the dump() value)

SELECT `b`.`virtuemart_product_id`,
       CASE WHEN `b`.`product_override_price` > 0 THEN `b`.`product_override_price`
            WHEN `b`.`product_discount_id` > 0 THEN `b`.`product_price` - (`b`.`product_discount_id` * `b`.`product_price` / 100)
            WHEN `b`.`product_price` < 1 THEN 1 ELSE `b`.`product_price`
            END AS `final_price`
FROM `vwxyz_virtuemart_product_categories` AS `a`
INNER JOIN `vwxyz_virtuemart_product_prices` AS `b` ON `b`.`virtuemart_product_id` = `a`.`virtuemart_product_id`
WHERE `a`.`virtuemart_category_id` = 1
ORDER BY `final_price`

And finally, here is the db-fiddle to prove that it works.

Answer 2

$query->select('a.virtuemart_product_id')
->from('#__virtuemart_product_categories AS a')
->select('(CASE WHEN '.$db->qn('b.product_override_price').' > 0 
THEN '.$db->qn('b.product_override_price').
' WHEN '.$db->qn('b.product_discount_id').' > 0 
THEN '.$db->qn('b.product_price').' - ('.$db->qn('b.product_discount_id').' * '.$db->qn('b.product_price').' / 100) 
WHEN '.$db->qn('b.product_price').' < 1 
THEN 1 ELSE '.$db->qn('b.product_price').' END)
as '.$db->qn('final_price'));

Answer 3

There is no Joomla based function for case/when/then construct, you have to use a simple select with mysql case statements in it.

$query->select("(CASE ...) as final_price");

Take a look at JDatabaseQuery API for a complete list of available database Joomla functions.