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
http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields.
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)* %>
<%= user.userid %>
<%= user.fname %>
<%= user.lname %>
<%= user.email %>
<%= user.phno %>
<%= rolename %>
<%= link_to 'Show', user ,:class => 'btn btn-default' %>
<%= link_to 'Edit', edit_user_path(user),:class => 'btn btn-default'
%>
<%= link_to 'Delete', user, method: :delete, data: { confirm: 'Are
you
sure?' } ,:style => 'color:#FFFFFF', :class => 'btn btn-danger'%>
<% end %>
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
<%= rolename[0] %> | 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
On 5 July 2015 at 11:50, Padmahas Bn [email protected] wrote:
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.
…
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.
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
On 5 July 2015 at 16:07, Padmahas Bn [email protected] wrote:
“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.
“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
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
On Sun, Jul 5, 2015 at 8:38 PM, Elizabeth McGurty [email protected]
On Jul 5, 2015, at 11:45 AM, Padmahas Bn [email protected] wrote:
I think as you said I have to specify association between tables so that it
would be easy to query without headache.
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:
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