# Select records from different model

**URL:** <https://www.ruby-forum.com/t/select-records-from-different-model/139634>\
**Category:** Rails\
**Created:** [June 5, 2008, 5:43pm UTC](https://www.ruby-forum.com/t/select-records-from-different-model/139634 "2008-06-05T17:43:25Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![psyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/848f3c/32.png) [@psyche](https://www.ruby-forum.com/u/psyche)\
**Post date:** [June 5, 2008, 5:43pm UTC](https://www.ruby-forum.com/t/select-records-from-different-model/139634/1 "2008-06-05T17:43:25Z")

</div>

Hi!

I’m new to the ruby on rails and have a question about find\_by\_sql  
method. I have two model A and B and I want to select all records from  
B with the A model.  
The following code: A.find\_by\_sql(“select \* from B”) returns nothing.  
Are there any methods to do it?

Thanks.

---

<div class="post-metadata">

**Author:** ![psyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/848f3c/32.png) [@psyche](https://www.ruby-forum.com/u/psyche)\
**Post date:** [June 5, 2008, 5:51pm UTC](https://www.ruby-forum.com/t/select-records-from-different-model/139634/2 "2008-06-05T17:51:41Z")

</div>

On 5 Jun 2008, at 16:42, psyche wrote:

> Hi!
> 
> I’m new to the ruby on rails and have a question about find\_by\_sql  
> method. I have two model A and B and I want to select all records from  
> B with the A model.  
> The following code: A.find\_by\_sql(“select \* from B”) returns nothing.

Foo.find\_by\_sql returns instances of Foo. What are you trying to do ?

Fred

---

<div class="post-metadata">

**Author:** ![psyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/848f3c/32.png) [@psyche](https://www.ruby-forum.com/u/psyche)\
**Post date:** [June 5, 2008, 5:51pm UTC](https://www.ruby-forum.com/t/select-records-from-different-model/139634/3 "2008-06-05T17:51:41Z")

</div>

oops thats very un-rails 😉  
(but should work)

first: are those tables related? Or why do you want to use A to retrieve  
B???

however why don’t you use

B.find(:all)

---

<div class="post-metadata">

**Author:** ![psyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/848f3c/32.png) [@psyche](https://www.ruby-forum.com/u/psyche)\
**Post date:** [June 5, 2008, 6:07pm UTC](https://www.ruby-forum.com/t/select-records-from-different-model/139634/4 "2008-06-05T18:07:36Z")

</div>

Thanks for your comments. Actually it was a bad example 🙂

I have a User and Role models with has\_and\_belongs\_to\_many  
relationship. I want to create named scope for admin users.

Something like this:  
named\_scope :admin, :joins =\> “LEFT JOIN roles\_users ON  
roles\_users.user\_id=users.id”, :conditions =\> { :role\_id =\>  
Role.find(:first, :conditions =\> { :name =\> ‘admin’ })}

But Rails returns the following sql: SELECT \* FROM `users` LEFT JOIN  
roles\_users ON roles\_users.user\_id=users.id WHERE (`users`.`role_id`  
= 1)

The bad thing: `users`.`role_id`. Are there any method to get  
`role_id` only?

On Jun 5, 7:51 pm, Thorsten M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![psyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/848f3c/32.png) [@psyche](https://www.ruby-forum.com/u/psyche)\
**Post date:** [June 5, 2008, 6:04pm UTC](https://www.ruby-forum.com/t/select-records-from-different-model/139634/5 "2008-06-05T18:04:03Z")

</div>

Actually it was a bad example 🙂

A have an User and Role model with has\_and\_belongs\_to\_many  
relationship and I want to create named\_scope for User model to  
extract only “admin” users.

Something like this: named\_scope :admin, :joins =\> “LEFT JOIN  
roles\_users ON roles\_users.user\_id=users.id”, :conditions =\>  
{ :role\_id =\> Role.find(:first, :conditions =\> { :name =\> ‘admin’ })}

Rails generate the following sql:  
SELECT \* FROM `users` LEFT JOIN roles\_users ON  
roles\_users.user\_id=users.id WHERE (`users`.`role_id` = 1)

The bad thing is `users`.`role_id`. Are there any methods to generate  
only `role_id`?

On Jun 5, 7:51 pm, Thorsten M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![psyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/848f3c/32.png) [@psyche](https://www.ruby-forum.com/u/psyche)\
**Post date:** [June 5, 2008, 7:17pm UTC](https://www.ruby-forum.com/t/select-records-from-different-model/139634/6 "2008-06-05T19:17:16Z")

</div>

Using named\_scope it would only one query (truthly: only one  
additional query on startup). Cool! 🙂

2008/6/5 psyche [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

---

<div class="post-metadata">

**Author:** ![psyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/848f3c/32.png) [@psyche](https://www.ruby-forum.com/u/psyche)\
**Post date:** [June 5, 2008, 7:08pm UTC](https://www.ruby-forum.com/t/select-records-from-different-model/139634/7 "2008-06-05T19:08:25Z")

</div>

I do not know is it a hack or a solution:  
User.scoped :joins =\> “LEFT JOIN roles\_users ON  
roles\_users.user\_id=users.id WHERE role\_id =  
{Role.find(:first, :conditions =\> { :name =\> “admin” }, :select  
=\> :id).id}”

Hope it helps somebody.

With “Role.find(:first…” I have two queries, but if admin role\_id  
would determined in the spec it would be only one query.

---

<div class="post-metadata">

**Author:** ![psyche](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/848f3c/32.png) [@psyche](https://www.ruby-forum.com/u/psyche)\
**Post date:** [June 6, 2008, 10:45am UTC](https://www.ruby-forum.com/t/select-records-from-different-model/139634/8 "2008-06-06T10:45:55Z")

</div>

ActiveRecord::Base.connection.select\_all(“select \* from users”)  
returns all rows.

2008/6/5 Kirill T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):