This allows ActiveAdmin::ResourceController::Sorting to sort by a column in another table

[Zequez]

The previous behavior always appended the current table name at the beginning of the column.
With this patch it only appends the current table name if the column was given without any specific table.

So now when you can call something like this:

column :name, sortable: 'people.name'

It works as expected. Though when loading the model it has to be joined to the other table, of course.

[gutenye]

I don't follow how this works?

class User
   belongs_to :school
end

# app/admin/user.rb
index do
  column :school, :sortable => 'schools.name'
end

# =>
Mysql2::Error: Unknown column 'schools.name' in 'order clause': SELECT  `users`.* FROM `users`  ORDER BY schools.name desc LIMIT 30 OFFSET 0

# it doesn't  join another table.

[dimitarvp]

Do this before the "index do" part:

# app/admin/user.rb
scope :joined, :default => true do |users|
  users.includes [:school]
end

Feel free to replace :joined scope with whathever you would call it, for example if you don't have another scopes, you might as well just call it "All".

Note: in the includes clause, you must use the singular form of the association. In the sortable clause, you must use the plural form.

[gutenye]

@Dimitko

It works. Thanks.

[gutenye]

@Dimitko

I found an issue about this way. It works normally. But when you use a filter, the code scope: joined, :default=>true do .. end is not called, so the sort is not work with a filter.

[reger]

I can't get this to work for me

I have this in my app/admin/service_cases.rb

scope :joined, :default => true do |service_cases|
  service_cases.includes [:subject]
end

and this in my service_cases index

column "Subject", :sortable => 'case_subjects.subject_text' do |service_case|
  service_case.case_subject.subject_text
end

All I get is this error:

Mysql2::Error: Unknown column 'service_cases.case_subjects.subject_text' in 'order clause'

Tried changing the column code to this, but I get the same result (and the undesired side effect of the text in the column being links now):

column "Subject/Problem", :case_subject, :sortable => 'case_subjects.subject_text'

[Zequez]

@reger that is what happened before this commit, are you using the latest version?

[dimitarvp]

@reger, you got a syntax error.

You should have used :sortable => :'case_subjects.subject_text'

In short, you did omit the colon before the composite sort key.

[reger]

@Zequez I'm using version 0.3.3 in my project.

[reger]

@Dimitko ah, I didn't notice (or realize that), I'll give that a try, thanks

[reger]

@Dimitko

tried it like you said:

column "Subject/Problem", :case_subject, :sortable => :'case_subjects.subject_text' 

same result:

Mysql2::Error: Unknown column service_cases.case_subjects.subject_text' in 'order clause'

[dimitarvp]

To better help you, let me quote a bigger part of my code:

ActiveAdmin.register Restaurant, { :sort_order => :name_asc }  do

  scope I18n.t(:all), :default => true do |dd|
    dd.includes [:city, :restaurant_type]
  end

...

index do
  column City.human_name, :city, :sortable => :"cities.name"

I think your table is in fact subjects, not case_subjects?...

[reger]

no, it's case_subjects, and each service_case has one

[dimitarvp]

In that case, this is wrong:

service_cases.includes [:subject]

Change it to this?

service_cases.includes [:case_subject]

[reger]

My apologies, there's a typo in my original code paste, I actually do have

service_cases.includes [:case_subject]

and still get the error

[dimitarvp]

Last thing out of the top of my head is -- you must use the GIT version of the gem in the Gemfile, like this:

gem 'activeadmin', :git => 'git://github.com/gregbell/active_admin.git'

Since this feature is not mainstream yet (read: not in any release version), hence you must use the GIT version. After you modify the Gemfile, do this on your console and inside your project's directory:

bundle update
bundle install
(both to be safe, in this order)

This should get you on track. :-)

[reger]

@Dimitko I think that's the problem. I was assuming this change was in 0.3.3 based on the dates, but apparently it's not in there. Thanks for the help (and patience).

[dimitarvp]

It's no problem, really. Did you actually made it to work?

[reger]

yes, I was able to get it to work with the correct code in there :)

[dimitarvp]

Pfew, about time. Glad I helped!

[Diosan]

Thank you for this... it will be a big help

[behrangsa]

Is there a way to do this without having this:

scope :joined, :default => true do |users|
  users.includes [:school]
end

I don't want a scope link, especially as it is not related to a special list of records.

[antek-drzewiecki]

Hey Reger, Diosan, Would you please share us how?

[Diosan]

I agree I don't like using the scope either... and it messes up my ability
to resort... maybe because i don't know what I am doing... but I do know it
is not creating the behaviour I desire.

On Thu, Jan 26, 2012 at 9:18 AM, Antek Drzewiecki <
[email protected]

wrote:

Hey Reger, Diosan, Would you please share us how?

---

Reply to this email directly or view it on GitHub:
#623 (comment)

[Diosan]

I think I am confused... this may not be what is cuasing the issue I am having... let me think about it abit and see whats up... for now assume my comment was erroneous

[pdf]

Requiring a scope to do sorting of belongs_to seems pretty hackish...

[dimitarvp]

Yeah I also don't agree with the scope thing a lot, but it's the best we got at the moment.

I am sure we would appreciate the author to share his plans on the matter. :-)

[denodaeus]

@gutenye

I have the same issue with filters -- works like a champ for sorting my custom column, but I can't filter on it because the scope isn't called for the filter.

I tried:

filter :"type", :as => :select, :sortable => 'events.type', :collection => Event.all(:group => "type", :order => "type desc", :select => "type").map(&:type)

to get the filter to display properly with the column data:

    column :calendar_type, :sortable => 'events.type'

Any thoughts on how to get the filter to work?

[sg552]

I also met the same "filter" problem that @gutenye , @denodaeus met. once I managed to "order" an column, the filter for this column is down, with an error message as far as I see illustrating that the scope we defined doesn't affect the filter. any solutions?

[nl0pvm]

Hi,

The suggested workaround using the scope works perfectly for me when it comes down to sorting. But! I have a table with multiple :belongs_to relations that I would like to show and that is where I run into trouble. I have tried so by defining multiple scopes but that does not work either. Any suggestions? Secondly is there already a fix for the @sg552, @gutenye and the @denodaeus filter" problem?

[sg552]

@nl0pvm nope, I kept the 'filter' function instead of 'sorting'. for now, I can only choose 1 of them, but not both.

[Lyricalpanda]

The work around shown above only works for resources that are declared as 'belongs_to'. I'm having an issue with a has_and_belongs_to_many (I have it working with identical code for a belongs_to resource). I get the following error:

Association named 'business' was not found; perhaps you misspelled it?

/models/user.rb

class User < ActiveRecord::Base

has_and_belongs_to_many :businesses

/admin/users.rb

scope :joined, :default => true do |users|
  users.includes [:business]
end

index do
  column 'Business', :sortable => 'businesses.name' do |user|
    if !user.businesses.blank?
      link_to("#{user.business_name(user.businesses.first)}", admin_business_path(user.businesses.first.id))
   end
end

[rabidpraxis]

For everyone that wanted to do this without a scope,

scope :joined, :default => true do |users|
  users.includes [:school]
end

You can just alter the scoped_collection to ensure that it includes what you need for your sorting / filtering

controller do
  def scoped_collection
    end_of_association_chain.includes(:school)
  end
end

[diegogaitan]

@rabidpraxis, you suggestion worked great for me. I used it by including two foreign keys:

 controller do
    def scoped_collection
      end_of_association_chain.includes(:from_location, :to_location)
    end
  end

Thanks!

[casiodk]

+1 for rabidpraxis´solution

[romeuhcf]

A general solution that avoids having unused joins on every query.

controller do
    def scoped_collection
      association = nil
      if table_to_order = @_params['order'].to_s.split('.').reverse[1]
        association = end_of_association_chain.reflect_on_all_associations(:belongs_to).find do |association|
          association.klass.table_name == table_to_order
        end
      end
      end_of_association_chain.includes(association.try(:name))
    end
  end

[alexventuraio]

@romeuhcf It worked for me, but can you explain or elaborate your solution a little bit more to clarify the advantages for using this instead of the most common end_of_association_chain.includes(:related_model)?

[backwardm]

Thank you @rabidpraxis for your note! That helped me be able to sort while in various scopes I have set up.