Best way to perform a certain DB query

Rails
1

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.

2

Hope this resolves:

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

On Dec 28, 3:30 pm, Rick P. [email protected]

3

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. [email protected]

4

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.

5

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?

6

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. [email protected]