santosh santosh - 6 months ago 11
SQL Question

Retrieving multiple Records,columns using Joins and pluck

I found a weird behaviour from Active Record Pluck.

My query is

Friend.joins('INNER JOIN users ON friends.friend_id = users.id').where("user_id=? AND (status=? or status=?)", 4,"true","").pluck("users.first_name, users.last_name")


It is to join with friends with users and get users first name and last name

Generated SQL command is

SELECT users.first_name, users.last_name FROM "friends" INNER JOIN users ON friends.friend_id = users.id WHERE (user_id=4 AND (status='true' or status=''))


If i run above command on sqlite browser tool

i am getting response like

first_name last_name

user4 y
user5 y


but from command line with pluck

["y", "y"]


and command line with find_by_sql

[#<Friend >, #<Friend >]


What's wrong in my code, or is it problem with pluck and find_by_sql

How can i resolve the problem?

thanks in advance

Answer

config/initializers/pluck_all.rb

module ActiveRecord
class Relation
  def pluck_all(*args)
  args.map! do |column_name|
    if column_name.is_a?(Symbol) && column_names.include?(column_name.to_s)
      "#{connection.quote_table_name(table_name)}.#{connection.quote_column_name(column_name)}"
    else
      column_name.to_s
    end
  end

  relation = clone
  relation.select_values = args
  klass.connection.select_all(relation.arel).map! do |attributes|
    initialized_attributes = klass.initialize_attributes(attributes)
    attributes.each do |key, attribute|
      attributes[key] = klass.type_cast_attribute(key, initialized_attributes)
    end
  end
end

end end

 Friend.joins('INNER JOIN users ON friends.friend_id = users.id').where("user_id=? AND (status=? or status=?)", 4,"true","").pluck_all("users.first_name","users.last_name")

resolves my issue, it's purely a pluck problem.

thanks for a great tutorial