Limits Offsets for Pagination partially works in redmine

[Eric Hayes]

When redmine attempts to show the last 10 things a user has performed,
the following error is displayed:

From:
http://127.0.0.1:3000/account/show/1

----------------
ActiveRecord::StatementInvalid in AccountController#show

DBI::DatabaseError: 37000 (156) [Microsoft][ODBC SQL Server Driver]
[SQL Server]Incorrect syntax near the keyword 'FROM'.: SELECT TOP 10
[journals].id FROM [journals] LEFT OUTER JOIN [issues] ON [issues].id
= [journals].journalized_id LEFT OUTER JOIN [projects] ON
[projects].id = [issues].project_id LEFT OUTER JOIN [journal_details]
ON journal_details.journal_id = journals.id WHERE
(journals.journalized_type = 'Issue' AND (journal_details.prop_key =
'status_id' OR journals.notes <> '')) AND (1=1 AND (journals.user_id =
1) AND (projects.status=1 AND EXISTS (SELECT em.id FROM
enabled_modules em WHERE em.name='issue_tracking' AND
em.project_id=projects.id))) GROUP BY [journals].id FROM [journals]
LEFT OUTER JOIN [issues] ON [issues].id = [journals].journalized_id
LEFT OUTER JOIN [projects] ON [projects].id = [issues].project_id
LEFT OUTER JOIN [journal_details] ON journal_details.journal_id =
journals.id WHERE (journals.journalized_type = 'Issue' AND
(journal_details.prop_key = 'status_id' OR journals.notes <> '')) AND
(1=1 AND (journals.user_id = 1) AND (projects.status=1 AND EXISTS
(SELECT em.id ORDER BY MIN(journals.id) DESC

RAILS_ROOT: C:/Ruby-Apps/Redmine/0.8-stable
Application Trace | Framework Trace | Full Trace

C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
connection_adapters/abstract_adapter.rb:147:in `log'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/
lib/active_record/connection_adapters/sqlserver_adapter.rb:840:in
`raw_execute'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/
lib/active_record/connection_adapters/sqlserver_adapter.rb:863:in
`raw_select'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.2.19/
lib/active_record/connection_adapters/sqlserver_adapter.rb:816:in
`select'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
connection_adapters/abstract/database_statements.rb:7:in
`select_all_without_query_cache'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
connection_adapters/abstract/query_cache.rb:59:in `select_all'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
connection_adapters/abstract/query_cache.rb:80:in `cache_sql'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
connection_adapters/abstract/query_cache.rb:59:in `select_all'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
associations.rb:1463:in `select_limited_ids_list'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
associations.rb:1453:in `add_limited_ids_condition!'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
associations.rb:1442:in
`construct_finder_sql_with_included_associations'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
associations.rb:1430:in `select_all_rows'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
associations.rb:1260:in `find_with_associations'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
associations.rb:1258:in `catch'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
associations.rb:1258:in `find_with_associations'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
base.rb:1343:in `find_every'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
base.rb:540:in `find'
vendor/plugins/acts_as_activity_provider/lib/
acts_as_activity_provider.rb:76:in `find_events'
C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.1.2/lib/active_record/
base.rb:1857:in `with_scope'
vendor/plugins/acts_as_activity_provider/lib/
acts_as_activity_provider.rb:75:in `find_events'
lib/redmine/activity/fetcher.rb:75:in `events'
lib/redmine/activity/fetcher.rb:74:in `each'
lib/redmine/activity/fetcher.rb:74:in `events'
lib/redmine/activity/fetcher.rb:73:in `each'
lib/redmine/activity/fetcher.rb:73:in `events'
app/controllers/account_controller.rb:35:in `show'
C:/Ruby/bin/mongrel_rails:19:in `load'
C:/Ruby/bin/mongrel_rails:19

----------------

However, when I remove the limit argument, everything works fine.
Here is the modification I made:

In \0.8-stable\app\controllers\account_controller.rb (the most recent
stable release of redmine)

Before Change (does not work):
Line 35: events = Redmine::Activity::Fetcher.new
(User.current, :author => @user).events(nil, nil) #, :limit => 10)

After Change (works, but displays a lot of items):
35: events = Redmine::Activity::Fetcher.new(User.current, :author
=> @user).events(nil, nil, :limit => 10)

However, pagination works correctly in other parts of the site. An
example page that uses pagination is here:

http://127.0.0.1:3000/projects/sampleproject/news

If I have more than 10 items here, then it is automatically broken up
into multiple pages. Here is the controller for the news item:

In \0.8-stable\app\controllers\news_controller.rb (the most recent
stable release of redmine)

def index
@news_pages, @newss = paginate :news,
:per_page => 10,
:conditions => (@project ?
{:project_id => @project.id} : Project.visible_by(User.current)),
:include => [:author, :project],
:order => "#
{News.table_name}.created_on DESC"
respond_to do |format|
format.html { render :layout => false if request.xhr? }
format.atom { render_feed(@newss, :title => (@project ?
@project.name : Setting.app_title) + ": #{l(:label_news_plural)}") }
end
end

Any ideas, patches, etc. would be greatly appreciated. Thanks!

[Ken Collins]

Unlike other databases, you must specify an :order string. SQL Server
needs that. Did you supply an order fragment?

- Ken

[Eric Hayes]

Yea tried specifying an order fragment. I still get an error though--
the generated sql is incorrect (such as the fragment GROUP BY
[journals].id FROM [journals] ). The whole generated sql is below:

[ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword
'FROM'.: SELECT TOP 10 [journals].id FROM [journals] LEFT OUTER JOIN
[issues] ON [issues].id = [journals].journalized_id LEFT OUTER JOIN
[projects] ON [projects].id = [issues].project_id LEFT OUTER JOIN
[journal_details] ON journal_details.journal_id = journals.id
WHERE (journals.journalized_type = 'Issue' AND
(journal_details.prop_key = 'status_id' OR journals.notes <> '')) AND
(1=1 AND (journals.user_id = 1) AND (projects.status=1 AND EXISTS
(SELECT em.id FROM enabled_modules em WHERE em.name='issue_tracking'
AND em.project_id=projects.id))) GROUP BY [journals].id FROM
[journals] LEFT OUTER JOIN [issues] ON [issues].id =
[journals].journalized_id LEFT OUTER JOIN [projects] ON [projects].id
= [issues].project_id LEFT OUTER JOIN [journal_details] ON
journal_details.journal_id = journals.id WHERE
(journals.journalized_type = 'Issue' AND (journal_details.prop_key =
'status_id' OR journals.notes <> '')) AND (1=1 AND (journals.user_id =
1) AND (projects.status=1 AND EXISTS (SELECT em.id ORDER BY MIN
(journals.id) DESC

Here is the line in account controller that is being called:

--------

events = Redmine::Activity::Fetcher.new(User.current, :author =>

@user).events(nil, nil, :limit => 10, :order => 'id')
----------

And here is the events fetcher code that is getting called:

--------------
# Returns an array of events for the given date range
def events(from = nil, to = nil, options={})
e = []
@options[:limit] = options[:limit]

@scope.each do |event_type|
constantized_providers(event_type).each do |provider|
e += provider.find_events(event_type, @user, from, to,
@options)
end
end

if options[:limit]
e.sort! {|a,b| b.event_date <=> a.event_date}
e = e.slice(0, options[:limit])
end
e
end

------------

[Ken Collins]

Yikes! This is some seriously heavy handed ruby for what could be a
simple finder. It even looks like you are doing your sort/limiting out
of the db and even out of somebasic ruby Enumerable methods.

My advice would be to break this down into simple chunks and tests so
that you are not pasting your code for others to debug. Also, are you
familiar with running unit tests and/or the ones for this project?
There are many tests passing for paginated results, even one with
multiple joins, so my gut reaction is this is a bug in your
implementations. If not, tests are the formal approach. On top of the
core ActiveRecord tests, we even have our own extensions for paginated
results in the adapter.

- Ken

[Eric Hayes]

Yea--it looks pretty messy to me too...this actually isn't my code.
I'm trying to get redmine working with SQL Server even though it isn't
officially supported yet. For the most part it works, but I run into
problems with dates and limits.

I'd like to change as little as possible to get this working, but I
don't mind making a couple small patches if the need arises. With my
limited ruby knowledge I'm not really sure where to begin, but I'll
see what I can do. Thanks for the help so far!