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.

Improve this question
0

1 Answer 1

Reset to default
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();
Improve this answer
2
  • 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. Commented Aug 7, 2019 at 22:57
  • Relevant posts: joomla.stackexchange.com/a/24867/12352 and joomla.stackexchange.com/a/23041/12352 Commented Aug 7, 2019 at 22:59

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.