WiceGrid 3.6.0.pre4 examples

Custom filters (joined tables)

There are two approaches to custom filters with joined tables. The first thing that comes to mind is to define a column of a joined table with :attribute and :assoc , and submit a list of all possible values of this column to :custom_filter , like it is done in the example below in the Priority column:

g.column name: 'Priority', attribute: 'name', assoc: :priority, custom_filter: %w(Anecdotic High Low Normal Urgent) do |task|

This works but such a filter produces a query with a WHERE clause comparing a varchar field with one of the string values submitted to :custom_filter . This is not guaranteed to be as efficient as comparing an indexed integer foreign key with an integer primary key, thus, this approach is highly advised against.

To implement filtering by foreign keys, define the column with the foreign key in :attribute and submit a hash or a two element array containing the IDs and labels of the joined table to :attribute . This has a negative side effect on sorting - the column will now be sorted according to the numerical value of the foreign key. It can be dealt with by overiding sorting by :custom_order in initialize_grid :

custom_order: {'tasks.status_id' => 'statuses.position', 'tasks.project_id' => 'projects.name'}

  • # encoding: utf-8
    class CustomFilters2Controller < ApplicationController
      def index
        @tasks_grid = initialize_grid(Task,
          include: [:priority, :status, :project],
          order: 'statuses.name',
          custom_order: {
            # 'tasks.priority_id' => 'priorities.name',
            'tasks.status_id'   => 'statuses.position',
            'tasks.project_id'  => 'projects.name'
          }
        )
      end
    end
    
  • .well
      %h2= current_page_title
      %p
        There are two approaches to custom filters with joined tables. The first thing that comes to mind is to define
        a column of a joined table with
        %code :attribute
        and
        %code :assoc
        , and submit a list of all possible values of this column to
        %code :custom_filter
        , like it is done in the example below in the Priority column:
      %p
        %code g.column name:  'Priority', attribute: 'name', assoc: :priority, custom_filter: %w(Anecdotic High Low Normal Urgent) do |task|
    
      %p
        This works but such a filter produces a query with a
        %code WHERE
        clause comparing a varchar field with one of the string values submitted to
        %code :custom_filter
        \.
        This is not guaranteed to be as efficient as comparing an indexed integer foreign key with an integer primary key, thus, this approach is highly advised against.
      %p
        To implement filtering by foreign keys, define the column with the foreign key in
        %code :attribute
        and submit a hash or a two element array containing the IDs and labels of the joined table to
        %code :attribute
        \.
        This has a negative side effect on sorting - the column will now be sorted according to the numerical value of the foreign key.
        It can be dealt with by overiding sorting by
        %code :custom_order
        in
        %code initialize_grid
        \:
      %p
        %code custom_order: {'tasks.status_id' => 'statuses.position', 'tasks.project_id' => 'projects.name'}
    
    = show_code
    
    .row-fluid
      .col-md-12
        = render   'grid'
  • <%= grid(@tasks_grid) do |g|
    
      g.column name:  'ID', attribute: 'id', filter: false
    
      g.column name:  'Title', attribute: 'title'
    
      g.column name:  'Priority', attribute: 'name', assoc: :priority, custom_filter: %w(Anecdotic High Low Normal Urgent) do |task|
        task.priority.name if task.priority
      end
    
      g.column name:  'Status', attribute: 'status_id',  custom_filter: Status.to_dropdown  do |task|
        task.status.name if task.status
      end
    
      g.column name:  'Project Name', attribute: 'project_id', custom_filter: Project.to_dropdown do |task|
        task.project.name if task.project
      end
    
      g.column  name:  'Archived', attribute: 'archived' do |task|
        task.archived? ? 'Yes' : 'No'
      end
    
      g.column name:  'Added', attribute: 'created_at' do |task|
        task.created_at.to_s(:short)
      end
    
      g.column   do |task|
        link_to('Edit', edit_task_path(task))
      end
    end -%>
IDTitlePriority StatusProject NameArchivedAdded

1-20 / 500 Alle rijen tonen
76totamAnecdoticNewUltimate WebsiteNo31 Mar 12:37Edit
116dolorem consecteturAnecdoticClosedUltimate WebsiteNo17 May 12:37Edit
51harum qui possimusAnecdoticCancelledDivine FirmwareNo26 Apr 12:37Edit
69similique placeat ipsaAnecdoticDuplicateDivine FirmwareNo08 May 12:37Edit
102inventore eligendi voluptasAnecdoticNewDivine FirmwareNo11 Jun 12:37Edit
106laboriosam enimAnecdoticStartedDivine FirmwareNo18 Jun 12:37Edit
43impedit atque minimaAnecdoticVerifiedSuper GameYes03 Jun 12:37Edit
46facereAnecdoticAssignedUltimate WebsiteYes26 Apr 12:37Edit
58atque etAnecdoticStartedDivine FirmwareNo28 May 12:37Edit
67officia quo maioresAnecdoticCancelledSuper GameNo15 Jun 12:37Edit
3aliquam repudiandaeAnecdoticAssignedDivine FirmwareNo21 Mar 12:37Edit
92est praesentiumAnecdoticClosedSuper GameNo08 Jun 12:37Edit
14repellat consequatur velitAnecdoticNewUltimate WebsiteNo06 May 12:37Edit
103molestiaeAnecdoticCancelledDivine FirmwareNo08 Jun 12:37Edit
2ut sintAnecdoticAssignedDivine FirmwareNo09 Apr 12:37Edit
22etAnecdoticCancelledSuper GameNo19 May 12:37Edit
23exercitationem autAnecdoticClosedDivine FirmwareNo23 Apr 12:37Edit
40dolorAnecdoticClosedSuper GameNo10 May 12:37Edit
132quiaAnecdoticCancelledUltimate WebsiteNo17 Mar 12:37Edit
129voluptates minima molestiaeAnecdoticVerifiedUltimate WebsiteNo03 May 12:37Edit

Fork me on GitHub