If a row of the first_table which matches the condition defined in the WHERE clause, but there is no row in the second_table which matches the condition defined in the ON condition, then rows for the second_table will get added with a NULL value for all columns. The condition defined in the LEFT JOIN clause is used to decide how to fetch rows from second_table. The first_table depends on all tables that are used in the LEFT JOIN condition except the second_table.The second_table depends on the first_table and all tables on which the first_table is depended on.Pic 4.First_table LEFT JOIN second_table JOIN CONDITION ![]() Sadly, there is no way to use the power of ActiveRecord here (like auto evaluating polymorphic relations), but still, the code looks readable enough. I rewrote this code by moving all activities filtering inside the join statement. …to fail (activity_type_id for such ‘ghost’ record would be NULL and comparison with NULL in SQL is neither TRUE nor FALSE – see this for more details) and eliminate the article from a result set.įortunately, there is an easy way out. This effectively will make the following SQL: activities.activity_type_id = 133 In a simplified explanation, PostgreSQL will attempt to “create” missing activity by filling its all attributes to NULL. ![]() If there is an article without any activity, as soon as you use “activities” table in you WHERE clause things go south. During tests, it turned out that when less than the selected value was chosen, articles with no activities disappeared, the same as if INNER JOIN was used instead of LEFT JOIN.Īfter some research (I recommend reading this article ) I figured out what was really happening. I was surprised when the above example did not work as expected. Example of SQL result of left_joins method with auto-generated join logic Which produces this SQL query (with some filter parameters added): Params validations were skipped in the code gist. Moreover, as described in the documentation, I had to add a group method when using having. 2 – INNER and LEFT JOIN comparison diagramĪctiveRecord offers a handy left_joins method (alias of left_outer_joins) that will handle all the logic of linking articles to activities and a having method that will allow filtering activity counts. The difference between those JOINS shows the picture below: Luckily, there are other types of JOIN statements in SQL which we can use like LEFT JOIN. In our case, this would result in excluding articles that were not viewed at all. However, there is a thing that needs to be remembered here – INNER JOIN selects only rows that have matching records in both tables. Typically, RoR developers use joins method to combine 2 tables together. The task looks quite simple but turned out to be a bit tricky. ![]() ![]() The activity model has a timestamp field created_at, while the article model has some more fields with details which we want to display to the users as title and body. The activity type model has a unique “name” field holding values like started, viewed, finished and so on. The article model has a polymorphic relation to the activity model (as activitable) which belongs to both user and activity type models. It looked like a standard every-day duty, but it turned out to be a good lesson on how LEFT JOIN really works.įirst, let’s take a brief look at the database: Some time ago I was given the task of adding a filter on a blog admin panel page to display articles by a count of views (less, equal, more than a given value) in a given period of time. This alone can create some challenges and may require you to use less common SQL statements in the Ruby on Rails apps like LEFT JOIN, hence I’ve decided to shed some light on it. This can be approached in several possible ways – one possible solution is to store them as an activity log in the database. You will learn: Left Join with filtering (Having, Count and Group By) in Ruby on RailsĮvery now and then you will need to keep a track of user activities in the system.
0 Comments
Leave a Reply. |