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

Question

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

Marcelinomarcell

try this Client.select("name as dname").map{|d| d.dname} – Foxtrot

@SantoshSharma I know this is possible but looping is not the best solution as we are already getting a list of dnames from database why do we need to again loop through every result.? – Marcelinomarcell

Answer 1

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)

Marcelinomarcell

Answer 2

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"]

Longspur

I am trying this approach on a similar model without any luck. @Longspur maybe you can lend some insight. scope :with_dud, -> { subquery = select("(renewal_date - current_date) AS days_until_due, #{table_name}.id").to_sql joins("JOIN (#{subquery}) as rd on rd.id = #{table_name}.id") } In console ---> Model.with_dud.first.attributes I see no days_until_due attribute listed... Sorry i can't make this comment formatted easier to read :( – Affiliate

@JoelGrannas Not sure if these merged fields show up in attributes, try Model.with_dud.first.days_until_due to verify whether the value is there. Maybe take a step back and reproduce the behavior in the original example, and experiment with that. – Longspur

@Longspur - thanks, this solution helped me with a similar issue. – Moynahan

Answer 3

Use this code:

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

Giraud

Answer 4

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"

Bekah

This is splendid. Thank you. Much simpler than joining in a temporary table with the data of interest. – Carlenecarleton