Arel and Ruby on Rails
Tags: ruby, rails, sql, database
Published: 2023-03-05 13:40:43

Hi RoR folks! Hope you are all doing well and celebrating Ruby's birthday!

Today in this post we are gonna talk about **Arel** and here is a list of what we are gonna discuss:

- What is Arel?
- History of Arel
- Advantages of using Arel
- Arel basic syntax
- Conclusion

## What is Arel?
Arel is a library that is being used to help us to write SQL queries in a much **easier**, more **readable**, and **cleaner** way. Also, it helps us to write both simple and complex queries.

---

## History of Arel
Arel was introduced in Rails 3 but it is now bundled into the [Active Record](https://rubygems.org/gems/activerecord) gem, and maintained in the [rails/rails](https://github.com/rails/rails) repository.

---

## Advantages of using Arel

- #### Readability
Since all SQL queries are strings, it may be hard to read, edit, or break down a complex query.

- #### Reusability
It’s much easier to reuse Arel queries as they are made up of interlinking nodes – a safer alternative than string interpolation.

- #### Reliability
If we join another table, our query will immediately break due to the ambiguity of the id column. Even if we qualify the columns with the table name, this will break as well if Rails decides to alias the table name.

---

## Arel basic syntax


#### - Arel Table
TTo start creating queries, we need to decide which Model we are going to query on. Then, we need to interact with the Arel Table for that Model and this could be done by using the `arel_table` method
```RUBY
users = User.arel_table
```

Here there is an `Arel::Table` object created for that table we are going to work on. After that object is created for that data table, Arel converts each column of that Table into an `Arel::Node` that we can execute different methods that are supported by this library on it. The most common methods in Arel are:

- Equal ***(eq)***
- Not Equal ***(not_eq)***
- Greater Than ***(gt)***
- Less Than ***(lt)***
- Greater Than Or Equal ***(gteq)***
- Less Than Or Equal ***(lteq)***
- Or ***(or)***
- And ***(and)***

> **NOTE:** To see all Arel predications you can run: `Arel::Predications.instance_methods`

---

#### - Where queries
We can say that more than 75% of all the queries are built using `where` method cause it's used to filter our data and select a specific part based on a specific true condition. Since `where` method can take different kinds of arguments, we can pass custom-built Arel queries to it and this could be so powerful.

> **NOTE:** To break a query to use Arel, there's a good rule to break out a method anywhere the word `AND` or `OR` is used.

```RUBY
class UserMembershipQuery
  def members
    users = User.where(public_profile.and(group_member))
  end

  private

  def table
    User.arel_table
  end

  def public_profile
    table[:private].eq(false)
  end

  def group_member
    table[:group_member].eq(true)
  end
end
```
```RUBY
users[:id].in([1,2,3]).to_sql
=> "`users`.`id` IN (1, 2, 3)"

users[:id].gt(2).to_sql
=> "`users`.`id` > 2"

users[:id].eq(3).to_sql
=> "`users`.`id` = 3"

User.where(table[:id].in([1,2,3])).to_sql
# SELECT "users".* FROM "users"  WHERE "users"."id" IN (1, 2, 3)
```

---

#### - Projection queries
Projection queries are the same as the `select` queries, we use the `project` to specify the nodes we want to select or return. This would need to be executed directly as SQL and can not easily be combined with ActiveRecord queries.

```RUBY
User.where(table[:role].eq(:admin)).project(:email).to_sql
=> "SELECT email FROM `users` WHERE `users`.`role` = 2"
```
And to connect these queries with the Active Record is a little bit more complex:
```RUBY
ActiveRecord::Base.connection.exec_query User.where(users[:role].eq(:admin)).project(:email)
```

---

#### - Joins
Here where we can see that Arel is really useful, cause writing join queries using Arel is a lot more simple and readable than SQL.

```RUBY
subscriptions = Subscription.arel_table
table.join(subscriptions)
```
Yes! It's that simple! We can also determine the relationship

```RUBY
table.join(subscriptions, Arel::Nodes::OuterJoin).on(table[:id].eq(subscriptions[:user_id]))
```
And to write join with a condition
```RUBY
table.joins(:subscriptions).merge(Subscription.where(active: true))
```

----

There are other methods that we can use to scope different queries:
Like `sum`, `average`, `count`, `min`, and `max` that we can use to perform different operations on values
```RUBY
table.project(table[:rate].average)
```

----

## Conclusion
In this article, we slightly discussed the basics of Arel like what's it and its history and its basic syntax but it has a lot more stuff that could be very helpful on, and there's a lot more to learn about it, so, I recommend you to go through [Arel documentation](https://www.rubydoc.info/gems/arel#description) and get your hand dirty and start using it.
If u have any comments or any feedback I will be happy to hear from your side.


Reviewed by **_Farha Zane_** ❤︎