Project list sorted after paging

Created by: Athou

The Project list on the dashboard seems to be sorted after the paging is done. This results in having projects recently updated on page 2 while they should be on top of page 1.

Created by: dzaporozhets

thank you. I'll take a look

By Administrator on 2012-10-10T18:59:28 (imported from GitLab)

Created by: erroric

I have fixed it on my project with this way:

def projects_with_events
   projects.order("(SELECT max(events.created_at) FROM events WHERE events.project_id = projects.id) DESC")
end

JOINs are big evil for big tables like "events", better way is using subqueries. I've just removed "events" table from inclusion and use tricky ORDER BY with subquery. If it need, events are included later automatically with couple of simple subqueries for each project in list on dashboard like this:

Event Load (1.9ms) SELECT "events".* FROM "events" WHERE "events"."project_id" = 71 AND (author_id IS NOT NULL) ORDER BY events.created_at DESC LIMIT 1

It is simple and faster than JOIN with "event" table containing many events.

ORDER BY with subquery statement also works fast:

Project Load (4.7ms) SELECT "projects".* FROM "projects" INNER JOIN "users_projects" ON "projects"."id" = "users_projects"."project_id" WHERE "users_projects"."user_id" = 19 ORDER BY (SELECT max(events.created_at) FROM events WHERE events.project_id = projects.id) DESC LIMIT 30 OFFSET 0

My solution also fixes bug with Postgresql and Arel - for projects.includes(:events).order("events.created_at DESC") and next @projects.page(params[:page]).per(30) Postgresql-builded query for finding project ids looks like:

SELECT DISTINCT "projects".id, events.created_at AS alias_0 FROM "projects" LEFT OUTER JOIN "events" ON "events"."project_id" = "projects"."id" INNER JOIN "users_projects" ON "projects"."id" = "users_projects"."project_id" WHERE "users_projects"."user_id" = 19 ORDER BY events.created_at DESC LIMIT 30 OFFSET 0

DISTINCT statement in Postgresql cannot be used without fields in ORDER BY statement (Postgresql ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list)

So, DISTINCT condition for this query applies to pair ("projects".id, events.created_at) and it is completely wrong with LIMIT statement. See my result example:

id | alias_0 ----+---------------------------- 68 | 2012-11-16 14:26:21.856801 71 | 2012-11-16 14:11:56.216941 71 | 2012-11-16 14:05:05.612409 68 | 2012-11-16 13:51:10.014374 70 | 2012-11-16 12:15:21.130512 70 | 2012-11-16 12:14:20.449143 70 | 2012-11-16 11:43:19.049767 70 | 2012-11-16 11:37:58.063816 70 | 2012-11-16 11:31:52.565269 70 | 2012-11-16 11:31:51.985533 70 | 2012-11-16 11:11:15.481988 70 | 2012-11-16 11:00:24.785196 70 | 2012-11-16 10:35:16.462932 70 | 2012-11-16 09:58:24.030933 70 | 2012-11-16 06:52:01.51427 70 | 2012-11-16 04:45:44.242613 71 | 2012-11-15 19:05:42.126636 71 | 2012-11-15 18:33:01.330417 71 | 2012-11-15 18:33:00.893586 71 | 2012-11-15 18:33:00.486967 71 | 2012-11-15 18:32:55.08535 71 | 2012-11-15 18:32:54.697937 71 | 2012-11-15 18:32:49.298219 71 | 2012-11-15 18:32:48.91025 71 | 2012-11-15 18:32:43.487037 71 | 2012-11-15 18:32:43.06214 71 | 2012-11-15 18:32:37.65892 71 | 2012-11-15 18:32:37.2541 71 | 2012-11-15 18:32:31.769459 71 | 2012-11-15 18:32:31.25003 (30 rows)

Project ids are duplicated and worse - some projects with old activity (more than 30 events ago) simply cut off from result and lose in projects list on dashboard.

P.S. subquery in ORDER BY statement works fine in sqlite3 and mysql as well.

By Administrator on 2012-11-16T16:45:44 (imported from GitLab)

Created by: qfox

Kewl!.. Thanks! Can you make pull request with your fix on this issue? It's easy to do: just make a fork, then make branch and then make pull request. Thanks again!

By Administrator on 2012-11-17T12:27:21 (imported from GitLab)

Created by: qfox

Great work!

By Administrator on 2012-11-16T19:26:25 (imported from GitLab)