$db = JFactory::getDbo();
// Create a new query object.
$query = $db->getQuery(true);
$fieldlist = $db->qn(array('category.id','category.title'), array('id', 'title')); // add the field names to an array
$fieldlist[0] = 'distinct ' . $fieldlist[0];
$query->select($fieldlist);
$query->from($db->quoteName('#__categories', 'category'));
$query->join('INNER', $db->quoteName('#__content', 'article') . ' ON (' . $db->quoteName('category.id') . ' = ' . $db->quoteName('article.catid') . ')');
$query->where("article.state = 1");
$query->order("title");
// Reset the query using our newly populated query object.
$db->setQuery($query);
// Load the results as a list of stdClass objects
$results1 = $db->loadObjectList();
$q = $db->getQuery(true);
$otherFieldlist = $db->qn(array('category.docman_category_id','category.title'), array('id', 'title')); // add the field names to an array
$otherFieldlist[0] = 'distinct 0-' . $otherFieldlist[0];
$q->select($otherFieldlist);
$q->from($db->quoteName('#__docman_categories', 'category'));
$q->join('INNER', $db->quoteName('#__docman_documents', 'document') . ' ON (' . $db->quoteName('category.docman_category_id') . ' = ' . $db->quoteName('document.docman_category_id') . ')');
$q->where("document.enabled = 1");
$q->union($query);
$q->order("title");
$db->setQuery($q); die($q);
Generate an exception :
Incorrect usage of UNION and ORDER BY SQL=SELECT distinct 0-
category.docman_category_idASid,category.titleAStitleFROM#__docman_categoriesAScategoryINNER JOIN#__docman_documentsASdocumentON (category.docman_category_id=document.docman_category_id) WHERE document.enabled = 1 ORDER BY title UNION ( SELECT distinctcategory.idASid,category.titleAStitleFROM#__categoriesAScategoryINNER JOIN#__contentASarticleON (category.id=article.catid) WHERE article.state = 1 ORDER BY title)
Because of parenthesis needed to encapsulate the first query !
(SELECT distinct 0-category.docman_category_idASid,category.titleAStitleFROM#__docman_categoriesAScategoryINNER JOIN#__docman_documentsASdocumentON (category.docman_category_id=document.docman_category_id) WHERE document.enabled = 1 ORDER BY title) UNION ( SELECT distinctcategory.idASid,category.titleAStitleFROM#__categoriesAScategoryINNER JOIN#__contentASarticleON (category.id=article.catid) WHERE article.state = 1 ORDER BY title)
SELECT distinct 0-category.docman_category_idASid,category.titleAStitleFROM#__docman_categoriesAScategoryINNER JOIN#__docman_documentsASdocumentON (category.docman_category_id=document.docman_category_id) WHERE document.enabled = 1 ORDER BY title UNION ( SELECT distinctcategory.idASid,category.titleAStitleFROM#__categoriesAScategoryINNER JOIN#__contentASarticleON (category.id=article.catid) WHERE article.state = 1 ORDER BY title)
Joomla! 3.6.5
Base de données : MySQL 5.7.19
| Labels |
Added:
?
|
||
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2017-10-20 10:21:11 |
| Closed_By | ⇒ | brianteeman |
Thank you for the response, I took 5 minutes to report this issue and you didn't even suggest something to resolve !!
This bug is related to the core of joomla and specially with (JDatabaseQueryElement, JDatabaseQuery, JDatabaseDriver)
Updating joomla is not even easy, I'm not working for my own project! this is a big project for a company !!
Instead making fast response, I suggest you to help with some solutions !
I did suggest something - update joomla
Thanks (y) You saved my life
If a bug can be confirmed on the current Joomla release (3.8.1 as of this moment), then we can investigate it and determine if a fix is needed. We will not spend time chasing a reported bug on a year old release because it is very well possible this issue has been previously reported and fixed, therefore the correct fix would be to update your Joomla site to receive the fix.
i think i remember this issue (fix for union and unionall included in J3.7.0)
so maybe i can help you !
here is the PR
#10817
and here is the fix that was made
https://github.com/joomla/joomla-cms/pull/10817/files
it seems easy to apply fix to J3.6.5
now after helping you above (i hope)
please note
Unfortunately upgrading is not possible in the short-term, I checked the same files on 3.8.1 and seem have the same code (nothing changed), I didn't check and I haven't had time to really investigate deeply (maybe I'll do it later or someone else can do it).
I fixed my problem with :
$union = $db->getQuery(true); $union->setQuery("($q) UNION ($query)");
Is not the best way to do it but it solved the issue !
Thank you (y)
Nothing changed ?
3186fe5#diff-099efd7df04a3182a918054c0b9f56d8
the change was included in 3.7.0-alpha1
Please update to Joomla 3.8.1 and retest. There have been hundreds of bug fixes since 3.6.5
Also please note that you are reporting an extension in com_docman which is a joomla extension and ot part of the core of joomla and you should report any issues with that component to its authors