Project list sorted after paging #1669

Closed
Athou opened this Issue Oct 10, 2012 · 4 comments

Projects

None yet

4 participants

@Athou
Athou commented Oct 10, 2012

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.

Owner
randx commented Oct 10, 2012

thank you. I'll take a look

erroric commented Nov 16, 2012

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.

zxqfox commented Nov 16, 2012

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!

zxqfox commented Nov 16, 2012

Great work!

@randx randx closed this Dec 3, 2012
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment