Menu
I have this code
User.find(:all, :limit => 10, :joins => :user_points, :select => "users.*, count(user_points.id)", :group => "user_points.user_id") which generates following sql
SELECT users.*, count(user_points.id) FROM `users` INNER JOIN `user_points` ON user_points.user_id = users.id GROUP BY user_points.user_id LIMIT 10 is it possible to make LEFT JOIN instead of INNER JOIN other way than User.find_by_sql and manualy typing the query?
936
You'll use INNER JOIN when you want to return only records having pair on both sides, and you'll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not.
If the tables involved in the join operation are too small, say they have less than 10 records and the tables do not possess sufficient indexes to cover the query, in that case, the Left Join is generally faster than Inner Join. As you can see above, both the queries have returned the same result set.
The reason why LEFT JOIN and INNER JOIN results are the same is because all the records of table branch has at least one match on table user_mast . The main difference between INNER JOIN and LEFT JOIN is that LEFT JOIN still displays the records on the the LEFT side even if they have no match on the RIGHT side table.
Different Types of SQL JOINsLEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
You can try this
User.find(:all, limit: 10, joins: "LEFT JOIN `user_points` ON user_points.user_id = users.id" , select: "users.*, count(user_points.id)", group: "user_points.user_id") If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
