1

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.

2

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();
  • I have moved your solution from the question to your answer. Please take the tour to begin to learning how to post/interact on this site. After an amount of time, you will be able to award your own answer the big green tick (please do this--it is important). For your information: 1. there is an innerJoin() method 2. The where() method accepts an array of conditions with a default glue of AND (this will likely improve the readability of your script) 3. Your ON condition does not need the extra parentheses to maintain logic. – mickmackusa Aug 7 at 22:57

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.