Topic: Creating SQL Joins to reduce SQL queries
Hi, me again:
Just looking for a way to either create a SQL join to reduce the number of SQL queries run.
Basically, now I have some clients which have an owner (from the users table).
I am producing a list of all clients and I want to print the name of the owner but I don't want to run a query for each client.
I would normally construct a query like:
SELECT c.*, u.surname AS owner_surname, u.forename AS owner_forename
FROM clients c
LEFT JOIN users u ON c.owner = u.id
At the moment I have a belongs_to setup:
belongs_to :owner, :class_name => "User", :foreign_key => "owner"
However, this is running a query for each client which isn't ideal (espically when you have lots of clients)
Client Load (0.001652) SELECT * FROM clients WHERE (clients.account_id = 1) ORDER BY name
Rendering within layouts/default
Client Columns (0.002466) SHOW FIELDS FROM clients
User Load (0.001473) SELECT * FROM users WHERE (users.`id` = 1)
User Load (0.001415) SELECT * FROM users WHERE (users.`id` = 1)
I've had a look through the Agile rails book and a quick google although can't seem to find anything useful.