Joins
In Clear, since every Collection is a SelectBuilder object, you can write very complex queries in a simple fashion. Let's see this example:
# Get the posts of the users which have more than X posts:
def user_with_more_than_x_posts(post_count)
User.query
.select("users.id as id")
.inner_joins("posts"){ posts.user_id == users.id }
.group_by("users.id")
.having{ raw("COUNT(*)") > post_count }
end
# Get the posts of the users with more than 10 posts:
Post.query.where{ user_id.in?(user_with_more_than_x_posts(10)) }
Joins are built using
inner_join, left_join, right_join, cross_join or simply join method. An optional block is requested for condition:# Retrieve users with supervisors
User.query.left_joins("users as u2"){ users.supervisor_id == u2.id }
Additionally, optional parameter
lateral can be set to true to create a LATERAL JOIN.Last modified 3yr ago