# Best way to perform a certain DB query

**URL:** <https://www.ruby-forum.com/t/best-way-to-perform-a-certain-db-query/124401>\
**Category:** Rails\
**Created:** [December 28, 2007, 11:30am UTC](https://www.ruby-forum.com/t/best-way-to-perform-a-certain-db-query/124401 "2007-12-28T11:30:19Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![rpflaum](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@rpflaum](https://www.ruby-forum.com/u/rpflaum)\
**Post date:** [December 28, 2007, 11:30am UTC](https://www.ruby-forum.com/t/best-way-to-perform-a-certain-db-query/124401/1 "2007-12-28T11:30:19Z")

</div>

Hello,

I’ve got two tables, users and books:

create\_table :users do |t|  
t.column :name, :string  
t.column :rich, :boolean  
t.column :book\_id, :integer  
end

create\_table :books do |t|  
t.column :name, :string  
end

If I have around half-million ‘rich’ User’s that read a very small  
amount of books, what is the best way I can query the database for the  
list of the names of these books?

I can perform User.find(:all, :conditions =\> [‘rich is true’], :include  
[:books]), but I believe this will return a very large number of users,  
and then I’d have to loop through each one to get the book names.

I’d appreciate any help! Thanks.

---

<div class="post-metadata">

**Author:** ![rpflaum](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@rpflaum](https://www.ruby-forum.com/u/rpflaum)\
**Post date:** [December 28, 2007, 1:24pm UTC](https://www.ruby-forum.com/t/best-way-to-perform-a-certain-db-query/124401/2 "2007-12-28T13:24:31Z")

</div>

Hope this resolves:

User.find(:all, :conditions =\> [‘rich is true’], :select =\> ‘distinct  
book\_name’, :include =\>[:books])

On Dec 28, 3:30 pm, Rick P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![rpflaum](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@rpflaum](https://www.ruby-forum.com/u/rpflaum)\
**Post date:** [December 28, 2007, 2:05pm UTC](https://www.ruby-forum.com/t/best-way-to-perform-a-certain-db-query/124401/3 "2007-12-28T14:05:32Z")

</div>

If you just need the names of books for rich users … then you need  
to add user\_id column to books table,  
then give your regular query for books table directly.

On Dec 28, 5:52 pm, Rick P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![rpflaum](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@rpflaum](https://www.ruby-forum.com/u/rpflaum)\
**Post date:** [December 28, 2007, 2:19pm UTC](https://www.ruby-forum.com/t/best-way-to-perform-a-certain-db-query/124401/4 "2007-12-28T14:19:13Z")

</div>

I forgot to mention that each user has only one book. Adding user\_id  
column to the books table will dramatically increase it’s size.

Sorry for the confusion.

---

<div class="post-metadata">

**Author:** ![rpflaum](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@rpflaum](https://www.ruby-forum.com/u/rpflaum)\
**Post date:** [December 28, 2007, 3:31pm UTC](https://www.ruby-forum.com/t/best-way-to-perform-a-certain-db-query/124401/5 "2007-12-28T15:31:08Z")

</div>

This works:

Book.find(:all, :select =\> ‘distinct name’, :joins =\> ‘join users on  
users.book\_id = books.id where users.rich is true’)

Can this query become prettier?

---

<div class="post-metadata">

**Author:** ![rpflaum](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@rpflaum](https://www.ruby-forum.com/u/rpflaum)\
**Post date:** [December 28, 2007, 1:52pm UTC](https://www.ruby-forum.com/t/best-way-to-perform-a-certain-db-query/124401/6 "2007-12-28T13:52:03Z")

</div>

Thanks for the reply Kiran.

This query still retrieves all of the rich users in the table. The book  
names are already unique, so the SQL ‘distinct’ keyword did not help.

Maybe the query has to be done in Books instead?

Kiran Soumya wrote:

> Hope this resolves:
> 
> User.find(:all, :conditions =\> [‘rich is true’], :select =\> ‘distinct  
> book\_name’, :include =\>[:books])
> 
> On Dec 28, 3:30 pm, Rick P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)