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)
.select(" as id")
.inner_joins("posts"){ posts.user_id == }
.having{ raw("COUNT(*)") > post_count }
# Get the posts of the users with more than 10 posts:
Post.query.where{ }


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 == }

Additionally, optional parameter lateral can be set to true to create a LATERAL JOIN.