Return Single Field with Rails Query

Question

I'm having trouble with something that should be so easy, but I don't know what I'm doing wrong. I'm simply trying to do a query that returns a single field instead of the complete record in Rails 3.

model method

def self.find_user_username(user_id)
  user_name = User.select("user_name").where(user_id)
  return user_name
end

I'm staring at the Rails Guide ( http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields) and it simply says (where viewable_by, locked = fields):

Client.select("viewable_by, locked")

I've tried flip flopping the select, like so:

User.select("user_name").where(user_id) - AND - 
User.where(user_id).select("user_name")

but neither work. In fact, I even tried:

user_name = User.select("yoda").where(user_id)

and I didn't get an error. When I look in the view, where I have:

Friend.find_user_username(friend.user_id)

I just keep getting the hash: ActiveRecord::Relation:0x2f4942f0

Answer 1

Rails 6 introduced ActiveRecord::Relation#pick.

pick(:foo) is equivalent to limit(1).pluck(:foo).first.

Answer 2

A lot of people referring to .pluck. It does have a caveat, it returns an array of the columns for records found.

If column is unique, like id, it's not an issue, DB will send to Active Record same content, 1 record 1 column, using limit(1) or not (using it is useless, and most certainly it will be discarded by DB query optimizer).

But if column is not unique, it could in fact be not the best performance. Because, first is called after AR executes DB query, returning [0] of all the results brought from DB. limit, is called before that execution and it's sent to DB as part of the query., filtering the results.

Consider the following table and setup:

> Log
=> Log(id: integer, user_id, integer, event_type: integer, timestamp: datetime)

> user_newest_logs = Log.where(user_id: 1).order(timestamp: :desc)

Now, let's get the last event date time for the user_id = 1:

1) this is the nicest way. DB returns one record with 1 column for timestamp

> user_newest_logs.limit(1).pluck(:timestamp).first
SELECT timestamp FROM logs WHERE logs.user_id = 1 ORDER BY logs.timestamp DESC LIMIT 1
=> Fri, 09 Aug 2019 23:00:00 UTC +00:00

2) DB returns one record with all columns

> user_newest_logs.first
SELECT * FROM logs WHERE logs.user_id = 1 ORDER BY logs.timestamp DESC LIMIT 1
=> #<Log:0x00111111>
 id: 920821839,
 user_id: 1,
 event_type: 1,
 timestamp: Fri, 09 Aug 2019 23:00:00 UTC +00:00

> user_newest_logs.first.timestamp
SELECT * FROM logs WHERE logs.user_id = 1 ORDER BY logs.timestamp DESC LIMIT 1
=> Fri, 09 Aug 2019 23:00:00 UTC +00:00

3) DB returns the list of timestamps for N records

> user_newest_logs.pluck(:timestamp)
SELECT timestamp FROM logs WHERE logs.user_id = 1 ORDER BY logs.timestamp DESC
=> [Fri, 09 Aug 2019 23:00:00 UTC +00:00,
Fri, 09 Aug 2019 22:00:00 UTC +00:00,
Fri, 09 Aug 2019 21:00:00 UTC +00:00,
Fri, 09 Aug 2019 20:00:00 UTC +00:00,
...
]

> user_newest_logs.pluck(:timestamp).first
SELECT timestamp FROM logs WHERE logs.user_id = 1 ORDER BY logs.timestamp DESC
=> Fri, 09 Aug 2019 23:00:00 UTC +00:00

> user_newest_logs.pluck(:timestamp).count    # NOT PRETTY HUH?!
=> 1523

So, if you do pluck(:column).first can be actually worst than just find_by.column.

Answer 3

there is a method called 'pluck' refer http://apidock.com/rails/ActiveRecord/Calculations/pluck

Answer 4

BTW if the question is about ActiveRecord::Relation it's the class of the object returned by where and select as it's said in the Rails Guide you're reading. It's optimized to be chained between several methods and you get the model object when you actually access an element of the association (so behind the scenes the SQL query is run just one time). This means you get the Relation object because you just return it.

In this case maybe the best solution is just use find and the access the user_name attribute

def self.find_user_username(user_id)
  User.find(user_id).user_name
end

The query will get all the other attributes from the DB but you'll return just the username.

Answer 5

User.where(:id => user_id).pluck(:user_name).first

Should do what you're trying to do.

pluck "accepts a column name as argument and returns an array of values of the specified column with the corresponding data type"

Answer 6

Your problem is not the select, it's the where. Your condition right now is essentially WHERE #{user_id}. If you executed this on the Rails console, you'd see something like this:

1.9.2p290 :003 > puts User.select('user_name').where(1).to_sql
SELECT user_name FROM "users" WHERE (1)

You need to pass the conditions as a hash to where, and take the first record as others suggested:

User.select('user_name').where(:id => user_id).first

Answer 7

You need to add .first to force the query to return a record because a where only returns an activerecord relation.

1.9.3p0 :048 > user = User.select('first_name').where('id = 1').first
  User Load (0.6ms)  SELECT first_name FROM `users` WHERE (id = 1) LIMIT 1
+------------+
| first_name |
+------------+
| Johnny     |
+------------+
1 row in set

1.9.3p0 :049 > user.first_name
=> "Johnny" 

Answer 8

Looks like that while not returning every field the query does return an object rather than the specific field's value. You can change the return of return user_name to return user_name.user_name.

def self.find_user_username(user_id)
  user_name = User.select("user_name").where(user_id)
  return user_name
end

Or you might see if User.user_name.where(user_id).first does what you need.