4

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');
2

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.

  • p.s. if you try to run the dumped sql in phpmyadmin, use ( before CASE and ) after END to avoid trouble with an "unexpected token". (no dramas with WorkBench or in the web application) – mickmackusa May 8 '18 at 2:03
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'));
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.