Activeadmin: Handle huge associations

[seanlinsley]

AA users are commonly frustrated by our naive building of dropdowns everywhere for a given association, because that option isn't scalable when you have millions of records.

The most obvious option is to allow the developer to blacklist certain models or tables:

ActiveAdmin.setup do |config|
  config.huge_db_tables = %w[tags categories user_actions]
end

The question then becomes: what do you replace it with?

As far as I can tell, there are three options:

• just remove it
• use AJAX
• use Rails' pluck method to just grab a name and an ID

So which would be best, as far as integrating into the official repo? (both for filters and forms)

[mupkoo]

Library like Chosen (with ajax) or Select2 can help a lot

[seanlinsley]

That means either forcing everyone to use one, or supporting all of the popular ones. That's a non-trivial amount of work for maintainers and people using AA, so I want to make sure we choose the right direction.

On Nov 11, 2013, at 5:36 PM, Mirko Akov [email protected] wrote:

Library like Chosen (with ajax) or Select2 can help a lot

\
Reply to this email directly or view it on GitHub.

[mupkoo]

They fit really nice into ActiveAdmin, and right now there are really easy to add (but without AJAX)

[shekibobo]

If I were to choose an external dependency on this, it would be Select2, since it supports Ajax out of the box.

[shekibobo]

That being said, I've never tried hooking up the filters to use Ajax. This project has too many interesting challenges:-)

[igorbernstein]

I don't see why you would need to force anyone to use it...can't it be just implemented as a formtastic input? that the developer can use via as: :autocomplete?

I will actually need this functionality in a couple of weeks and was planning to take a crack at it after #2656
Here are a couple of ideas I had in terms of the api/implementation that I had brewing in the back of head:

 # search by title, use standard AA rules for the display text, fill in the id
autocomplete :title

# search by title or isbn using standard display rules of AA
autocomplete :isbn_title, on: [:isbn, :title]

# custom search by isbn or title. (Ideally supporting select2's infinite scroll)
autocomplete :featured_editions do
  Edition.where(featured: true).where("title LIKE '?%'", params[:q]).page(params[:page]).per(20)
end

# search by isbn or title, but use the cover_with_title partial to render each row
autocomplete :isbn_title, on: [:isbn, :title], partial: 'cover_with_title'

# completely override the generation of results
autocomplete :custom do
  query = Edition.some_crazy_query
  render json: query.as_json ...
end

form do |f|
  f.input :edition, as: :autocomplete, source: :featured_editions
end

Then package up the results as simple json wrapper of { id: 1, html: 'whatever was generated' }, then a custom formatResult function can extract html and render each result.

sidenote: select2 is a much better choice since chosen wasn't designed for ajax

[shekibobo]

:+1: on autocomplete DSL. Probably just use Ransack by default underneath with :title_cont or :isbn_or_title_cont when you pass in the field or array of fields.

Edit

Couldn't we also just use the association's resource index.json page passing in the Ransack query if a register for the resource exists? And then you can customize using the autocomplete syntax to optimize and customize the return.

[seanlinsley]

Yeah, we'd probably want to use the built-in JSON API to look up records. I've done this recently:

select1 = fieldset.find 'select:first' # Companies
select2 = fieldset.find 'select:last'  # Users
select1.change(->
  $.get '/admin/users.json', q: {company_id_eq: $(@).val()}, (data)->
    select2.html data.map (u)-> """<option value="#{u.id}">#{u.name}</option>"""
).change()

Which was nice :)

[igorbernstein]

I need to be able to easily customize the appearance of the autocomplete options. The simplest way I've thought to do that is to rely on server side partials & have the server send html, as opposed to the server sending json and having the javascript generate html.
Ideally I would much rather generate html on the client side but I don't wanna resort to client side templating, any ideas?

[igorbernstein]

@shekibobo
I hope you don't mind, but I'll respond to your comment from #2656 "Select2 is actually pretty simple to integrate in ActiveAdmin on its own" here, to keep all autocomplete info in one place.

You are right, integrating select2 into ActiveAdmin isn't difficult, but it involves a some boilerplate. I've prototyped it in of my apps, but when its mixed with stylized row rendering & somewhat involved queries, it becomes a little ugly. I would like to extract the plumbing into ActiveAdmin to make the customized row rendering & queries a little more apparent.

[igorbernstein]

well, crap in a hat, formtastic forces you to define custom inputs in the root namespace(::) or Formtastic::Inputs::
https://github.com/justinfrench/formtastic/blob/master/lib/formtastic/helpers/input_helper.rb#L353-L360

[seanlinsley]

We override it :)

[seanlinsley]

I'm going to take this off of the 1.0.0 milestone because this is blocked by #2638 & Kaminari

[igorbernstein]

Can you clarify if this issue is about:

1. a select box having a lot of options
2. a parent having a lot of children
3. a parent having a lot of children that have a lot options

I'm pretty sure I can solve 1 without dealing w. #2638 & kaminari. Which will improve (but not solve) the situation with 2 & 3.

what date are you shooting for 1.0 release?

[seanlinsley]

Hmm, actually this issue is specific to associations so it isn't really blocked by #2638. I'll put this back on the milestone.

For the different subjects you mentioned, I'm not sure I understand what you mean by "have a lot of options". When would you ever have a select box with a huge number of options that aren't parent or child associations?

[igorbernstein]

example of each type I mentioned:
1 - favorite_movie select box, where the user picks 1 favorite movie out of 1000's

f.input :favorite_movie

2 - a gender select box of each employee for a hollywood studio (2 options, but a lot of select boxes)...basically:

has_many :employees |f|
  f.input :gender
end

3 - a successful actor's filmography (a lot of credits with lots of options for movies)

has_many :credits do |f|
  f.input :movie
end

[seanlinsley]

This ticket is all about resolving the performance issues caused by the association dropdowns we currently build for filters and forms. If we can add a feature like searchable AJAX fields while fixing this, then all the better.

Or: of the three types you described, this is all about type number one.

[igorbernstein]

I haven't had a large enough time block to finish implementing this yet, but here's what I'm working on:

It might not have been obvious that my original proposal defined the autocomplete configuration on the related child, which I decided to move away from....there is no need to register a full ActiveAdmin resource just to be able to autocomplete a relationship any more than there's a need to register an AA resource for a related has_many child. I also decided to drop support the explicit render variant....that use case is better handled by overriding the action in the controller block.

Furthermore, if we define the autocomplete config on the actual resource then the FormBuilder can automatically use the autocomplete input. Also, this can be expanded to be used with filters in #2738. Here is an updated spec:

ActiveAdmin.register Post do
  # search for authors by first & last name
  autocomplete :author, on: [:first_name, :last_name]
  form do |f|
    # will be enhanced with the autocomplete input because its configured above
    f.inputs :author
end

# syntax can be extended to apply for nested inputs
ActiveAdmin.register Category do
  autocomplete 'posts/author', on: [:first_name, :last_name]

  form do |f|
    f.has_many :posts do |p|
      p.inputs :author
    end

    f.inputs :title, :body, :author, for: :posts
  end
end

# query can be overriden to use something like ThinkingSphinx
# the implementation will look for will_paginate/kaminari last_page/next_page/page/total_page
# methods on the result to enable infinite scroll
ActiveAdmin.register Post do
  autocomplete :author do |r|
    User.search(r.query, page: r.page, per_page: 20)
  end
end


# Infinite scroll can be manually controlled
ActiveAdmin.register Post do 
  autocomplete :author do |r|
    query = User.where("first_name LIKE '?%'", r.query).limit(21).offset(r.page * 20)
    results = query.slice(0, 20)
    has_more = query.length > 20

    r.respond_with results, more: has_more
  end
end

# builtin query can be accessed
ActiveAdmin.register Post do 
  autocomplete :author, on: [:first_name, :last_name] do |r|
    autocomplete_query r
  end
end


# row rendering can be easily configured
ActiveAdmin.register Post do 
  autocomplete :author, on: [:first_name, :last_name], template: 'author_row'
end

# selection & choice templates can be separated
ActiveAdmin.register Post do 
  autocomplete :author, on: [:first_name, :last_name], selection_template: 'author_row_selection', choice_template: 'author_row_choice'
end

# possible API in response to #2738
ActiveAdmin.register Post do
  autocomplete :author, on: [:first_name, :last_name], for: :filter
end


# possible API in response to #2738 to suggest non-relational fields
ActiveAdmin.register Author do
  autosuggest :first_name, for: [:filter, :form]
end

Current implementation direction:

#active_admin/autocomplete/controller.rb

module ActiveAdmin
  module Autocomplete
    module Controller
      # render html for selections, called via ajax on page load & on choice selection
      def autocomplete_selections
        ids = params.fetch(:ids, '').split(",")
      end

      # render html for choices
      def autocomplete_choices
        term = params[:q]
        page = params[:page]
      end

      # helper to generate query for the current config
      def autocomplete_query(context)
      end
    end

    # object wrapper to capture request parameters and provide DSL to deal with infinite scroll
    class Context
      attr_reader :query, :page, :config_name
      def respond_with(results, opts={})
      end
    end
  end
end

# active_admin/autocomplete/form_build_extension.rb
module ActiveAdmin
  module Autocomplete

    module FormBuilderExtension
      def input(method, *args)
        if has_auto_complete_config_for_method
          opts = args.extract_options!
          opts[:as] ||= :autocomplete
          super method, *args, opts
        else
          super
        end
      end
    end

  end
end

[dmitry]

Hope that somebody will look into that feature.

[jacobwilliams918]

Recently ran into this issue and have been having quite some trouble reach a solution using Select2/AJAX to solve this issue. Any word on the development status of this?

[rogerkk]

Here's another one hoping for progress on this. :-)

[dmitry]

Is there are a clear spec, how it should be implemented?

I can invest my time to implement this feature.

[tobsch]

+1

[drush]

@igorbernstein Your approach looks pretty interesting. Have you gotten anyone else to collaborate with you on completing the spec prototype? Is this code in a branch/PR somewhere?

[ryanwaggoner]

+1

[franklsf95]

Would be a really nice addition. Now I need to manually exclude all the huge associations from generating huge dropdown lists.

[osmancrush]

+1

[Epiju]

+1

[wkoffel]

+2

[StyleOffDev]

+1

[kiote]

+1

[dmitry]

Plus one, +1 or :+1: cannot help. Instead please tell how would you like to use it and how the DSL should look like.

[kiote]

DSL here seems to be pretty clear, what do you think?

[aaronchi]

+1 for this DSL

[mwlang]

Is anything being done with this or is it sitting idle?

[dmitry]

@mwlang nothing done since: https://github.com/activeadmin/activeadmin/issues/2692#issuecomment-29355831

[raldred]

Proposed DSL seems fairly clean, I'd be happy to proceed. It seems consensus is all good. I'm interested in picking up where @igorbernstein left off and getting this implemented.

[mwlang]

I thought about doing it myself, but I'm tight on time at the moment due to project deadlines. However, if you get started on it, you've got a keen collaborator/tester in me.

[dmitry]

@raldred :+1: could be tested by me aswell.

[seanlinsley]

I'd be happy to help test.

Coming back to this ticket, I think I'd prefer this syntax instead of providing the catch-all autocomplete DSL.

filter :user, as: :autocomplete, on: [:first_name, :last_name]

f.input :user, as: :autocomplete, on: [:first_name, :last_name]

It requires a little duplication, but I'd prefer to keep it consistent with the existing DSL. An added benefit is that you can decide what order the filters and form fields should show up in.

[seanlinsley]

If Postgres is in use, we can easily get approximate row counts to automatically detect large tables.

[seanlinsley]

Maybe we should make use this instead of reinventing the wheel: https://github.com/activeadmin/activeadmin/issues/1754#issuecomment-131555996

[raldred]

Possibly, I actually like your recent proposal, seems consistent like you say with what users are used to.
I'll look into both approaches.

[fabn]

I want to share my current implementation (working in my project), it could be abstracted to work in every situation.

I'm using select2 at version 4.0.0 which handles normal select inputs instead of needing custom markup for remote datasources.

I created a gist to avoid including a lot of code here, I explain what my code currently does here.

The core classes (that I think can be reused here) are a custom input for forms and a custom filter class.

They add some css classes (used in javascript code as selectors) and compute the collection values. If record is new the association is empty, if submitted or edited it only loads current values to prefill select2 options.

The html_options[:data][:'ajax-url'] part tells select2 the endpoint to query for available completions, in my case I've used a single controller for all completable classes.

Finally there is a javascript file that can be partially reused. My implementation relies on format returned by Model.completions which is implemented with elasticsearch. Currently my completable models have a macro like this

class Person < ActiveRecord::Base
  # Allow person to be completed with ES.
  acts_as_completable on: :full_name
end

My forms use the custom input in this way:

  # This model belongs_to :author, class_name: Person
  f.input :author, as: :ajax_select

My filters are declared in this way

filter :author, as: :ajax
filter :category, as: :ajax
filter :keywords, as: :ajax, multiple: true

I think this can work pretty well with the DSL above. The only missing part is to implement the resource DSL part. If one want to use my approach (single parametric controller that handle all completions) the autocomplete call should add a method to that controller.

Otherwise (better, imho) the proposed DSL can be implemented as described and in my inputs the html_options[:data][:'ajax-url'] should be changed accordingly. A good default could be

html_options[:data][:'ajax-url'] ||= options[:completion_path] || default_completions_path

# This will provide a default value if no completion path is given, it's computed using association
# class and complete prefix.
def default_completions_path
  # data_type == User => complete_users_path
  # when massaged by select2 code final url will become /users/complete?q=prefix
  template.url_for([:complete, data_type])
end

# Change it to return model to complete
def data_type
  reflection_for(method).klass
end

Hope to help in the implementation and finally have this in core AA.

[feng88724]

Here's another one hoping for progress on this. :-)

[seanlinsley]

@raldred have you had time to work on this?

[raldred]

@seanlinsley sorry not had chance to push on with this yet, i'm just back from a break.
The project that I'm using to drive this is just starting so I hope to get on it very soon. Will keep you posted with my progress here.

[Darhazer]

+1

[terry90]

+1

[sebin]

+1

[dmitry]

Please guys, use emotions, in other case those +1 doesn't matter anything:

[epitron]

A nice plugin that solves this problem was released at the beginning of this year: https://github.com/holyketzer/activeadmin-ajax_filter

It supports autocomplete for associations in the form, as well as for filters on the index pages. It's quite simple to configure as well.

[phillipoertel]

+1

[blocknotes]

IMO one-to-many and many-to-many associations selections are a weak point in ActiveAdmin.

If it can help: some times ago I made a plugin which integrates selectize.js (I didn't know of activeadmin-ajax_filter plugin when I started, which seems quite good).
Now I'm working on another plugin with the same intent: to improve the association selection (similar to RailsAdmin).

[seanlinsley]

4132 should make it so scopes with counts are usable on large tables.