What is wrong with my joomla query syntax?

by Oktarin   Last Updated August 06, 2019 15:10 PM

For some reason query I put directly to DB works, but when set in Joomla syntax is returning only 1 result, and there should be a lot more...

Clear Mysql query:

SELECT 
vmp.virtuemart_product_id as 'product_id',
vmp.product_parent_id as 'parent_id',
vmp.product_sku as 'sku',
vmp.product_in_stock as 'stock',
vmp.published as 'published',
pc.virtuemart_category_id as "category"
FROM `mus_virtuemart_products` as vmp
JOIN mus_virtuemart_product_categories as pc ON pc.virtuemart_product_id=vmp.product_parent_id
WHERE vmp.product_parent_id>0 AND pc.virtuemart_category_id=105;

Joomly query:

      $query->select(array(
                    'vmp.virtuemart_product_id as product_id',
                    'vmp.product_parent_id as parent_id',
                    'vmp.product_sku as sku',
                    'vmp.product_in_stock as stock',
                    'vmp.published as published',
                    'pc.virtuemart_category_id as category'
                    ))
       ->from($db->quoteName('#__virtuemart_products','vmp'))
       ->join('INNER', $db->quoteName('#__virtuemart_product_categories','pc') . 
        ' ON (' . $db->quoteName('pc.virtuemart_product_id') . ' = ' . $db->quoteName('vmp.virtuemart_product_id') . ')')        
       ->where($db->quoteName('pc.virtuemart_category_id') . ' = '. $db->quote($category). ' AND '. $db->quoteName('vmp.product_parent_id') . ' >0');

I have tried various combinations, and am always getting only 1 result. I have limited to only 1 WHERE condition that I possible casuse problem:

->where($db->quoteName('vmp.product_parent_id') . ' >0');  

I have tried to set "zero" as $db->quote($zero) where $zero="0", but didn't change anything...

Thanks all for help



Related Questions


Updated June 01, 2015 13:04 PM

Updated October 25, 2016 09:04 AM

Updated May 18, 2015 21:04 PM