gitlabhq/gitlabhq — pull request #1489 by jirutka (2012-09-18 14:32:29+00:00)
Title: Fix query for wiki pages listing to work on all databases

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).

```sql
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.

```sql
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.