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

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.