$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_id
ASid
,category
.title
AStitle
FROM#__docman_categories
AScategory
INNER JOIN#__docman_documents
ASdocument
ON (category
.docman_category_id
=document
.docman_category_id
) WHERE document.enabled = 1 ORDER BY title UNION ( SELECT distinctcategory
.id
ASid
,category
.title
AStitle
FROM#__categories
AScategory
INNER JOIN#__content
ASarticle
ON (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_idAS
id,
category.
titleAS
titleFROM
#__docman_categoriesAS
categoryINNER JOIN
#__docman_documentsAS
documentON (
category.
docman_category_id=
document.
docman_category_id) WHERE document.enabled = 1 ORDER BY title) UNION ( SELECT distinct
category.
idAS
id,
category.
titleAS
titleFROM
#__categoriesAS
categoryINNER JOIN
#__contentAS
articleON (
category.
id=
article.
catid) WHERE article.state = 1 ORDER BY title)
SELECT distinct 0-
category.
docman_category_idAS
id,
category.
titleAS
titleFROM
#__docman_categoriesAS
categoryINNER JOIN
#__docman_documentsAS
documentON (
category.
docman_category_id=
document.
docman_category_id) WHERE document.enabled = 1 ORDER BY title UNION ( SELECT distinct
category.
idAS
id,
category.
titleAS
titleFROM
#__categoriesAS
categoryINNER JOIN
#__contentAS
articleON (
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