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.
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.
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
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.
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.
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
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.
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.
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.
#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.
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?