CODEWITHSUNDEEP
sqlruby-on-railspostgresqlruby-on-rails-4activerecord
dnsh
dnsh

Reputation: 3633

How to pluck "as alias_name" from rails active record query

I have this query:

Client.select("name as dname")

Which is working fine.

Client.select("name as dname").first.dname
=> "Google"

Now I want to get all dnames as an array but pluck method does not work as dname is not column name.

2.2.5 :040 > Client.select("name as dname").pluck(:dname)
   (0.6ms)  SELECT dname FROM "clients"
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "dname" does not exist

How to get array of dnames? Is there any method like pluck which works on column name alias which is defined using as.

I can do this 

Client.select("name as dname").map{|d| d.dname}

But looping through every record is not making any sense to me

Upvotes: 14

Views: 11291

Answers (4)

route
route

Reputation: 1972

If you are computing a virtual column or aliased column you should use this form with Arel.sql otherwise column should exists in the table. For example for concating two columns into one virtual one:

Client.pluck(Arel.sql("one_column || two_column"))

and the SQL:

SELECT one_column || two_column FROM "clients"

Upvotes: 1

dnsh
dnsh

Reputation: 3633

Well my understanding of pluck was wrong. from apidock I understood that

Use pluck as a shortcut to select one or more attributes without loading a bunch of records just to grab the attributes you want.

So,

Client.select("name as dname").pluck(:dname)

Should be written like this

Client.pluck(:name)

Upvotes: 15

Epigene
Epigene

Reputation: 3908

select and pluck don't play well together, but I've use a workaround that joins the aliased column onto the query object, allowing pluck. I usually write joins like this as scopes starting with with_

class Client
  scope :with_dname , -> {
    # Build a subquery SQL snippet
    # Since we will be joining it onto the base table, we need to select the id column as well
    subquery = select("name AS dname, #{table_name}.id").to_sql

    # join the subquery to base model
    joins("JOIN (#{subquery}) as addendum ON addendum.id = #{table_name}.id")
  }
end

# this will work 
Client.with_dname.first.pluck(:dname) #=> ["Google"]

# this may be more efficient
Client.limit(1).with_dname.first.pluck(:dname) #=> ["Google"]

Upvotes: 2

Uday kumar das
Uday kumar das

Reputation: 1613

Use this code:

Client.select("name as dname").map{|d| d.dname}

Upvotes: 1

Related Questions