Skip to content

Fix query for wiki pages listing to work on all databases (ie. incl. PostgreSQL)

Matthias Käppler requested to merge github/fork/jirutka/fix-wiki-pg into master

Created by: jirutka

ActiveRecord query Wiki.group("slug").order("created_at") in this case generates ambiguous group by query violating SQL standard: “The columns in a select list must be in the group by expression or they must be arguments of aggregate functions.” Therefore it doesn’t work on truly SQL compliant databases such as PostgreSQL (and almost all except MySQL and SQLite3).

SELECT wikis.* 
FROM wikis 
GROUP BY slug 
ORDER BY updated_at;

PostgreSQL has clause DISTINCT ON for these situations, sadly MySQL doesn’t. Well, I rewrote this query to another form that should work on every SQL database including MySQL.

SELECT wikis.* 
FROM wikis 
WHERE wikis.project_id = ?
    AND wikis.created_at IN (
        SELECT MAX(wikis1.created_at) AS max_id 
        FROM wikis wikis1 
        WHERE wikis.project_id = wikis1.project_id
            AND wikis.slug = wikis1.slug)
ORDER BY created_at;

I used ActiveRecord::Relation and ARel for this query so the code doesn’t look very nice, but it’s the best way how I was able to write it and it works well. Moreover, it’s actually scope!

Related to #554 (closed).

Merge request reports