Selecting specific fields with "where" clause

I am trying to retrieve only one field instead of all field. Hence guides.rubyonrails.org suggest to use Model.select(“field_name, separated_by, comma”) here. But the thing is this will produce the SQL Select field_name FROM Model. I want to extend this to include where clause.

Why I want to do this

This is the code in my index page

			<% @users.each do |user| %>

			<% **rolename = Role.where(id: user.roleid).pluck(:role_name)** %>

			<tr>

				<td><%= user.userid %></td>

				<td><%= user.fname %></td>

				<td><%= user.lname %></td>

				<td><%= user.email %></td>

				<td><%= user.phno %></td>

				<td><%= rolename %></td>

				<td><%= link_to 'Show', user ,:class => 'btn btn-default' %></td>

				<td><%= link_to 'Edit', edit_user_path(user),:class => 'btn btn-default' %></td>

				<td><%= link_to 'Delete', user, method: :delete, data: { confirm: 'Are you sure?' } ,:style => 'color:#FFFFFF', :class => 'btn btn-danger'%></td>

			</tr>

			<% end %>

		</tbody>

The large font code after loop is giving the result what I want but the result is enclosed with big brackets and double quotes like this [" result"]. Because that code will return an array of values.

So I found I have to use Model.select to retrieve single object. Then I tried <% rolename = Role.select(“role_name”).where(id: user.roleid) %> but its returning some invalid value like 00x30000658487.

Again I tried with this <% rolename = Role.find_by_sql(“SELECT role_name FROM ROLES, USERS WHERE roles.id = users.roleid.to_i”) %>, its giving error PG::UndefinedTable: ERROR: missing FROM-clause entry for table “roleid”

By comparing all types of queries I thought it is good to include where clause along with Model.select. But couldn’t find anywhere.

Please help me

Thank you

Temporarily I’m using <td><%= rolename[0] %> </td> to eliminate brackets and double quotes.

But there should be better way to do this. Its an obvious situation where we have to select specific fields of a record which satisfies some condition.

All I want is Active Record query equivalent to this

SELECT role_name FROM role WHERE role.id = user.roleid

Thank you

Try Role.where(id: user.roleid).select("role_name")

Or even better user.role.select("role_name")

Though I have to ask why you need to do the select? Why not just use Role.where(id: user.roleid).role_name or again even better user.role.role_name

It is not likely that the slight saving in processor/memory will make any difference in practice to the application performance. I always recommend not complicating code by attempting to optimise until it proves to be necessary. Almost always the bottlenecks in an application will not be in the area you expect.

Colin

Before I respond, would you please verify the fields in your table User, specfically roleid.

Before I respond, would you please verify the fields in your table User, specfically roleid.

Elizabeth,

I checked the fields in the database. They exist and as I’ve posted in the question, the code <% rolename = Role.where(id: user.roleid).pluck(:role_name) %> working but returns an array.

Thank you

Hello Colin these are the results I got

Try

Role.where(id: user.roleid).select(“role_name”)


#<Role:0x000000061d80e8>
An invalid value. As I stated in my question this is similar to **<% rolename = Role.select("role_name").where(id: user.roleid) %>** but "where" and "select" are inter changed. So same invalid value.

Or even better

user.role.select(“role_name”)

private method `select’ called for nil:NilClass

Wouldn’t work in my case because, I think it requires an Association between user and role table. And there Must be a field on both tables named “role_name”. Which doesn’t exist

Though I have to ask why you need to do the select? Why not just use

Role.where(id: user.roleid).role_name

undefined method `role_name’ for #Role::ActiveRecord_Relation:0x00000005ff4218

or again even better

user.role.role_name

undefined method `role_name’ for nil:NilClass

This also need same field name role_name in both tables which doesn’t exist.

Thank you

End it with .first

Perfect. It worked <% rolename = Role.where(id: user.roleid).pluck(:role_name).first %>

And got another way from Stackoverflow

<% rolename = Role.find(user.roleid).role_name %>

Thank you

Hello Colin these are the results I got

>Try >Role.where(id: user.roleid).select("role_name")

#<Role:0x000000061d80e8>

An invalid value. As I stated in my question this is similar to <% rolename = Role.select("role_name").where(id: user.roleid) %> but "where" and "select" are inter changed. So same invalid value.

Sorry, should be Role.where(id: user.roleid).select(:role_name)

Swapping the order of method calls completely changes the meaning. Role.select().where() fails because Role.select returns an array, and one cannot call where on an array. Role.where() returns an ActiveRecord::Relation, and it is valid to call select on that.

>Or even better >user.role.select("role_name")

private method `select' called for nil:NilClass Wouldn't work in my case because, I think it requires an Association between user and role table. And there Must be a field on both tables named "role_name". Which doesn't exist

Not role_name, but there there should be a relation between the two. If you have a field in user which maps to the id of a role then conventionally this would be called role_id and you would specify User belongs_to role Role has_many users Then you could do as I have suggested.

I think you could benefit from working right through a good tutorial such as railstutorial.org (which is free to use online) which would show you the basics of rails.

Colin

Even though it sounds technically right, I don’t know why rails showing some junk values for some of the query you suggested and some of the query I tried.

Again there is no exception but this is the result: #Role::ActiveRecord_Relation:0x00000005f4f5d8

I think as you said I have to specify association between tables so that it would be easy to query without headache.

Thank you

Yes, but for your own understanding of this, you need to stop thinking in terms of tables -- they are an implementation detail in Rails (unless you are dealing with a legacy schema). The relationship is between Model classes, not the tables that persist them. Define both sides of the relationship in your models, and Rails will make the rest of it trivial to reason about.

Walter

#Role::ActiveRecord_Relation:0x00000005f4f5d8 is not an error or junk value, it’s actually information. Informing you that you have created an ActiveRecord::Relation instance of class Role.

When this object is returned, try to run to iterate through it (for xxx in object … content… end). You’ll see that it has database content for Role.

Read more about ActiveRecord at: rails/activerecord at main · rails/rails · GitHub

Padmahas,

Do you understand the notion of specifying associations between tables in Rails? Let’s me know and I’ll try to explain? In general and in anticipation of your answer do you understand INNER JOINS in SQL?

Liz