Selecting specific fields with "where" clause

[Padmahas Bn]

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

<tbody>

<% @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

[Padmahas Bn]

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

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
To post to this group, send email to rubyonra...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/5d915337-b273-49b5-a93d-1583679be8fc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Elizabeth McGurty]

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

[Colin Law]

On 5 July 2015 at 11:50, Padmahas Bn <padm...@gmail.com> 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

[Padmahas Bn]

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

[Padmahas Bn]

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

[Elizabeth McGurty]

End it with .first

[Padmahas Bn]

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

--

You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
To post to this group, send email to rubyonra...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/b4d7aea0-5dcd-4d3b-b175-0a1cf7be84f7%40googlegroups.com.

[Colin Law]

On 5 July 2015 at 16:07, Padmahas Bn <padm...@gmail.com> wrote:
> 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

[Padmahas Bn]

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

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
To post to this group, send email to rubyonra...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAL%3D0gLtE%2BCCD3RMvnZZWpaYMGnOXEeZz6g7_ePvm8UKXWxqBCA%40mail.gmail.com.

[Walter Lee Davis]

On Jul 5, 2015, at 11:45 AM, Padmahas Bn <padm...@gmail.com> 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

[Elizabeth McGurty]

 #<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: https://github.com/rails/rails/tree/master/activerecord

[Elizabeth McGurty]

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

On Sunday, July 5, 2015 at 11:46:46 AM UTC-4, Padmahas Bn wrote: