[#6073] - MSSQL: SQL Error when creating/editing a group
- Closed
- 21 Feb 2015
- Medium
- Build: master
- # 6073
It seems like this is a generic problem. Not matter in what area you want to create or edit a group, whether in articles, user notes, banners, contacts etc., you always get a sql error like this one:
Error [Microsoft][SQL Server Native Client 11.0][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.SQL=SELECT DISTINCT a.id AS value, a.title AS text, a.level, a.published FROM ( SELECT DISTINCT id,title,level,published,parent_id,extension,lft,rgt FROM mmj38_categories WHERE (extension = 'com_content' OR parent_id = 0) AND published IN (0,1) ORDER BY lft ASC) AS a LEFT JOIN [mmj38_categories] AS b ON a.lft > b.lft AND a.rgt < b.rgt LEFT JOIN [mmj38_categories] AS p ON p.id = 19 WHERE NOT(a.lft >= p.lft AND a.rgt <= p.rgt)
Consequently you get an error where a group is assigned to an item eg. when creating/editing an article.
joomla-cms-bot
- | Labels |
Added:
?
|
||
zero-24
- | Category | ⇒ | MS SQL |
Of course:
This is what I get when I create a new article. When you analyze the error message I becomes clear that mssql-server - in my case version 2008R2 - is not able to execute a subquery with an order by clause. Consequently the category tree is empty.
You can go to administrator\components\com_categories\models\fields\categoryedit.php line 97 $subQuery->order('lft ASC') and comment that out. Then the article mask loads without an error but the category tree is not sorted.
If haven´t tested this with later versions of mssql server but I will when I have more time.
can you test this dirty trick
SELECT DISTINCT a.id AS value, a.title AS text, a.level, a.published, a.lft
FROM (
SELECT DISTINCT id,title,level,published,parent_id,extension,lft,rgt
FROM mmj38_categories
WHERE (extension = 'com_content' OR parent_id = 0)
AND published IN (0,1)
) AS a LEFT JOIN [mmj38_categories] AS b
ON a.lft > b.lft AND a.rgt < b.rgt
LEFT JOIN [mmj38_categories] AS p
ON p.id = 19 WHERE
NOT(a.lft >= p.lft AND a.rgt <= p.rgt)
ORDER BY a.lft ASC
The query works but the result set comprises only 11 items versus 25 items before.
It was a simply test to understand how mssql works I need a little bit more time for a solution
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2015-02-21 12:37:03 |
Sorry for my dumbness can you clarify a little bit more when this issue arise maybe adding some screenshot
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/6073.