Feat - select distinct #25

Closed
marcelfalliere opened this Issue Dec 26, 2010 · 6 comments

Projects

None yet

8 participants

@marcelfalliere

Well I wanted to fetch only distinct model object, comparates by only two attributes (while the table has 10+ attributes). I will share here my unobstrosive solution, without forking, if someone is interested.

It's pretty simple so I won't add anything. Here's the code :

yourmodel.rb :

#overrired of where for auto complete
def self.where(arg0)

    tabIds = []

    # traiter pour le autocomplete mais laisser la possibilité pour les autres
    case arg0
        when Array; tabInit = Place.find_by_sql("select * from Places where #{sanitize_sql_array(arg0)}")
        else tabInit = Place.find_by_sql("select * from Places where #{arg0}")
    end

    # enlever les doublons
    tabInit.each_with_index do |place, i|
        doublonFound = false
        tabInit.each_with_index do |place2, j|
            if j > i && !doublonFound
                doublonFound = place.eql?(place2)
            end 
        end
        if !doublonFound
            tabIds << place.id
        end
    end

    # retourner un truc que limit() ou autre fonction AR pourra traiter
    return super.where("id in (?)", tabIds)
end

def eql?(other)
    return self.name==other.name && self.where==other.where
end
@thuss
thuss commented Feb 20, 2011

When I want custom autocompletion that goes beyond the features of the current plugin (such as selecting distinct entities) I implement my own autocomplete method on the controller. This just replaces the "autocomplete :organization, :city" call you would normally put in your controller.

For example to autocomplete "distinct" cities I do this:

def autocomplete_organization_city
  term = params[:term]
  if term && !term.empty?
      items = Organization.select("distinct city").
          where("LOWER(city) like ?", term.downcase + '%').
          limit(10).order(:city)
   else
     items = {}
   end
   render :json => json_for_autocomplete(items, :city)
end
@zapnap
zapnap commented May 22, 2011

I ran into this today. It's actually really easy to accomplish now with the :scopes feature that was added. For example:

class Organization < ActiveRecord::Base
  scope :uniquely_named, group(:name)
end

class OrganizationsController
  autocomplete :organizations, :name, :scopes => [:uniquely_named]
end
@sohooo
sohooo commented Jul 26, 2011

@zapnap, this works fine with sqlite3, but Postgres doesn't like this statement (tested on Heroku):

# in class Exercise < ActiveRecord::Base
scope :uniquely_named, group(:name)

# Log Output
ActiveRecord::StatementInvalid (PGError: ERROR:  column "exercises.id" must appear in the GROUP BY clause or be used in an aggregate function
SELECT  exercises.id, exercises.name FROM "exercises" WHERE (LOWER(exercises.name) ILIKE '%supp%') GROUP BY name ORDER BY exercises.name ASC LIMIT 10):
@wenbert
wenbert commented Apr 16, 2012

I have the same problem with @sohooo

@zapnap's solution worked perfectly with sqlite but I got errors in production when using postgres

How do we make this work with pgsql?

UPDATE: Although longer, the solution by @thuss above is good and unobtrusive

def autocomplete_organization_city
  term = params[:term]
  if term && !term.empty?
      items = Organization.select("distinct city").
          where("LOWER(city) like ?", term.downcase + '%').
          limit(10).order(:city)
   else
     items = {}
   end
   render :json => json_for_autocomplete(items, :city)
end
@creyes
creyes commented Nov 8, 2012

This solution works pretty good, thank you

@bigtunacan bigtunacan closed this Feb 20, 2014
@pr0d1r2
pr0d1r2 commented May 5, 2015

This works for me:

  # model
  scope :distinct_on_name, lambda {
    select('DISTINCT ON (models.name) models.name, models.id')
  }
  # controller
  autocomplete :model, :name, scopes: [:distinct_on_name]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment