Raw sql query returns multiple rows, but with Joomla methods only one row is returned

Question

For some reason the query I put directly to DB works, but when set in Joomla syntax it 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:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$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');
$db->setQuery($query);
$podaci = $db->loadAssocList();

I have tried various combinations, and am always getting only 1 result. I think I have limited the issue to 1 condition in the WHERE clause:

->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.

---

UPDATE 1

After a few iterations in my code I start getting an error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

When I change my where condition to only $db->quoteName('pc.virtuemart_category_id') . ' = '. $db->quote($category) the query works ok.

Answer 1

I found the error in my query.

Original:

JOIN mus_virtuemart_product_categories as pc ON pc.virtuemart_product_id=vmp.product_parent_id

Joomla query:

->join('INNER', $db->quoteName('#__virtuemart_product_categories','pc') . 
    ' ON (' . $db->quoteName('pc.virtuemart_product_id') . ' = ' . $db->quoteName('vmp.virtuemart_product_id') . ')')

• The last column reference should be replaced.

Instead of vmp.virtuemart_product_id it should be vmp.product_parent_id

So the final correct query is:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$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.product_parent_id') . ')')        
      ->where($db->quoteName('pc.virtuemart_category_id') . ' = '. $db->quote($category). ' AND '. $db->quoteName('vmp.product_parent_id') . ' >0');
$db->setQuery($query);
$podaci = $db->loadAssocList();