5

I'm receiving a MYSQL Error on this code in ExpressionEngine 1.6.4 (very old version)

{exp:query  limit="10" paginate="bottom"
    sql="SELECT 'gallery' AS `content_type`, `cat_id` AS `entry_id`, `recent_entry_date` AS `entry_date`
        FROM `exp_gallery_categories`
        WHERE `gallery_id` = 9 AND total_files > 0
        UNION
        SELECT 'video' AS `content_type`, `entry_id`, `entry_date`
        FROM `exp_weblog_titles`
        WHERE `weblog_id` = 6 ORDER BY `entry_date` DESC"

}
<p>{content_type} - {entry_id} - {entry_date format="%d %F %Y"}</p>
{paginate}{pagination_links}{/paginate}
{/exp:query}

MySQL returns this:

MySQL ERROR: Error Number: 1054
Description: Unknown column 'entry_date' in 'order clause'
Query: SELECT COUNT(*) AS count FROM `exp_gallery_categories` WHERE `gallery_id` = 9 AND total_files > 0 UNION SELECT COUNT(*) AS count FROM `exp_weblog_titles` WHERE `weblog_id` = 6 ORDER BY `entry_date` DESC

It seems like EE is modifying my query before MySQL and making it fail (On a SQL client this works smoothly).

5
  • 1
    Are you able to run the same query from within the EE CP? Go to Tools->Data->SQL Manager and click the Query Form tab. I have a feeling (but can't back it up with any solid evidence yet) that the Query module only allows very basic Select queries. You may have to resort to building a small add-on to execute your custom query. – foamcow Dec 10 '12 at 13:27
  • The query works and returns the expected results in the Query Form. – Angus Dec 10 '12 at 13:58
  • Looking at the only 1.x source I have sitting around (1.7.3) the only validation the query module does it to check that the first 6 chars of the query are SELECT, otherwise it just passes the contents of the sql param to the DB (!) via $DB->query($sql). Are you able to update your EE version, even on a test platform to see if it resolves this? Also, as an aside, I'm sure you know this already but there are some fairly significant XSS vulnerabilities that have been patched since 1.6.4. If this is a production site you should seriously consider updating it to the latest version. – Tom Davies Dec 10 '12 at 15:17
  • Could this be done as a JOIN rather than a UNION? - even if just to see if you can get it to work. – foamcow Dec 10 '12 at 15:39
  • I think the culprit is pagination! With a (different) JOIN query it returns the same error. If I remove the line {paginate}{pagination_links}{/paginate} everything works but I get all the results without pagination. – Angus Dec 10 '12 at 19:32
4

What's Going Wrong

You've already spotted the problem but here's a quick explanation of why the error is occurring. If you enable pagination EE1's query module will issue two queries - one calculates the total number of results sans-pagination and the other issues the query itself with an additional LIMIT parameter.

The total results query is generated by rewriting the SELECT portion of your query as SELECT COUNT(*). This means that the entry_date alias is no longer created, so MySQL trips up as soon as it hits the ORDER BY part of the query.

Another problem is that even if you remove the ORDER BY parameter the union query results in two different count values being returned - one for each table. Presumably EE would ignore all but the first value.

How To Fix It (Hopefully)

I haven't tested this patch but it's the same approach that EE2's query module uses so it should work. Open up mod.query.php and find the following line:

$query = $DB->query(preg_replace("/SELECT(.*?)\s+FROM\s+/is", 'SELECT COUNT(*) AS count FROM ', $sql));

Replace it with this:

$query = $DB->query("SELECT COUNT(*) AS count FROM ({$sql}) AS query");

This approach leaves your original query intact, so it should return accurate total results counts and won't be anywhere near as easily tripped up by aliases or unions.

0
1

Tell MySQL which entry_date you want to use. Something like:

{exp:query  limit="10" paginate="bottom"
    sql="SELECT 'gallery' AS `content_type`, `cat_id` AS `entry_id`, `recent_entry_date` AS `entry_date`
        FROM `exp_gallery_categories` g
        WHERE `gallery_id` = 9 AND total_files > 0
        UNION
        SELECT 'video' AS `content_type`, `entry_id`, `entry_date`
        FROM `exp_weblog_titles` t
        WHERE `weblog_id` = 6 ORDER BY `t.entry_date` DESC"

}

It's been awhile, but I'll assume that the gallery also has an entry_date field and you just haven't specified which field you need or want.

1
  • 1
    I would like to mix the results of both tables and order by the resulting table's 'entry_date' field. I already tried SELECT * FROM (SELECT...UNION SELECT..) as t ORDER BY t.entry_date – Angus Dec 10 '12 at 19:36

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.