Skip to content

Instantly share code, notes, and snippets.

@mike1011

mike1011/arel_cheatsheet_on_steroids.md

Forked from ProGM/arel_cheatsheet_on_steroids.md
Created October 25, 2022 21:13
Show Gist options
  • Save mike1011/eca318b2ca3aae35e9a2e7b8761dad3f to your computer and use it in GitHub Desktop.
Save mike1011/eca318b2ca3aae35e9a2e7b8761dad3f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @ProGM ProGM revised this gist Sep 8, 2021. 1 changed file with 9 additions and 0 deletions.
    9 changes: 9 additions & 0 deletions arel_cheatsheet_on_steroids.md
    View file
    Original file line number Diff line number Diff line change
    @@ -30,6 +30,15 @@ posts[:id].maximum # MAX(`posts`.`id`)
    posts[:rating].average # AVG(`posts`.`rating`)
    ```

    ### Math operations
    ```ruby
    (posts[:answers_count] + posts[:likes_count]).as('engagement') # (`posts`.`answers_count` + `posts`.`likes_count`) AS engagement
    posts[:answers_count] * 2 # (`posts`.`answers_count` * 2)
    # If you want to sum more than 2 columns, it's a little bit trickier
    (posts[:col_a] + posts[:col_a]).expr + posts[:col_c] # `posts`.`col_a` + `posts`.`col_b` + `posts`.`col_c`
    ```


    ### Mapping custom SQL functions with Arel

    ```ruby
  2. @ProGM ProGM revised this gist Feb 2, 2021. 1 changed file with 25 additions and 0 deletions.
    25 changes: 25 additions & 0 deletions arel_cheatsheet_on_steroids.md
    View file
    Original file line number Diff line number Diff line change
    @@ -125,6 +125,31 @@ users[:id].count.over(window) # COUNT(`users`.`id`) OVER (PARTITION BY `users`.`
    ```
    More examples here: https://github.com/rails/rails/blob/master/activerecord/test/cases/arel/select_manager_test.rb#L728

    ### Extending Arel

    ```ruby
    class ArelExt::SumIf < Arel::Nodes::NamedFunction
    def initialize(condition, column)
    super('SUM', [Arel::Nodes::NamedFunction.new('IF', [condition, column, 0])])
    end
    end

    module ArelSumIfConcern
    include ActiveSupport::Concern

    def sum_if(condition)
    ArelExt::SumIf.new(condition, self)
    end
    end

    Arel::Attributes::Attribute.include ArelSumIfConcern
    ```

    Usage:
    ```ruby
    User.arel_table[:comments_count].sum_if(User.arel_table[:first_name].matches("gian%")).to_sql # SUM(IF(`users`.`first_name` LIKE 'gian%', `users`.`comments_count`, 0))
    ```


    ## Loooots of examples directly in the tests:

  3. @ProGM ProGM revised this gist Feb 6, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion arel_cheatsheet_on_steroids.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    # Arel Cheatsheet on Steroids
    A complete cheatsheet for Arel, including NamedFunction functions, raw SQL and window functions.
    A (more) complete cheatsheet for Arel, including NamedFunction functions, raw SQL and window functions.

    ## Tables
    ```ruby
  4. @ProGM ProGM created this gist Feb 6, 2020.
    131 changes: 131 additions & 0 deletions arel_cheatsheet_on_steroids.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,131 @@
    # Arel Cheatsheet on Steroids
    A complete cheatsheet for Arel, including NamedFunction functions, raw SQL and window functions.

    ## Tables
    ```ruby
    posts = Arel::Table.new(:posts)
    posts = Post.arel_table # ActiveRecord
    ```
    ### Table alias
    ```ruby
    posts = Post.arel_table.alias(:user_posts) # `posts` `user_posts`
    ```

    ## Columns

    ### Base
    ```ruby
    posts[:id] # `posts`.`id`
    posts[:title] # `posts`.`title`
    posts[Arel.star] # `posts`.*
    posts[:title].as('name') # `posts`.`title` AS name
    ```

    ### Grouping functions
    ```ruby
    posts[:id].count # COUNT(`posts`.`id`)
    posts[:answers_count].sum # SUM(`posts`.`id`)
    posts[:id].minimum # MIN(`posts`.`id`)
    posts[:id].maximum # MAX(`posts`.`id`)
    posts[:rating].average # AVG(`posts`.`rating`)
    ```

    ### Mapping custom SQL functions with Arel

    ```ruby
    users = User.arel_table

    Arel::Nodes::NamedFunction.new(
    'IF',
    [users[:name].eq(nil), users[:email], users[:name]]
    ).as('name_or_email') # IF(`users`.`name` IS NULL, `users`.`email`, `users`.`name`) AS name_or_email

    # Wrapping value for NamedFunctions
    Arel::Nodes.build_quoted('some value')
    # Example
    Arel::Nodes::NamedFunction.new('DATE_FORMAT', [posts[:created_at], Arel::Nodes.build_quoted('%Y%m')]) # DATE_FORMAT(`posts`.`created_at`, '%Y%m')
    ```

    ## Select
    ```ruby
    posts.project(:id, :title) #=> "SELECT id, title FROM posts"
    posts.project(posts[:id], posts[:title]) #=> "SELECT `posts`.`id`, `posts`.`title` FROM `posts`"
    posts.project(posts[Arel.star]) #=> "SELECT `posts`.`*` FROM `posts`"
    ```

    ## Conditions
    ```ruby
    posts[:id].eq(3) # `posts`.`id` = 3
    posts[:name].eq(nil) # `posts`.`name` IS NULL
    posts[:answers_count].gt(3) # `posts`.`answers_count` > 3
    posts[:answers_count].lteq(3) # `posts`.`answers_count` <= 3
    posts[:id].in(2, 3, 4) # `posts`.`id` IN (2, 3, 4)
    posts[:title].matches('hell%') # `posts`.`title` LIKE 'hell%'
    ```

    ### AND/OR
    ```ruby
    posts[:id].eq(3).and(posts[:name].eq('hello')) # `posts`.`id` = 3 AND `posts`.`name` = 'hello'
    users[:id].eq(2).and(users[:last_name].eq('doe').or(users[:first_name].eq('john'))) # `users`.`id` = 2 AND (`users`.`last_name` = "doe" OR `users`.`first_name` = 'john')
    ```

    ### Wrap raw SQL in arel:
    ```ruby
    Arel.sql('`posts`.`id` = 3') # Wrapping condition
    ```

    ## Joins
    ```ruby
    comments = Comment.arel_table
    posts.join(comments) # INNER JOIN `comments`
    posts.join(comments).on(posts[:id].eq(comments[:post_id])) # INNER JOIN `comments` ON `posts`.`id` = `comments`.`post_id`
    ```

    ### LEFT OUTER JOINS
    ```ruby
    posts.join(comments.alias(:post_comments), Arel::Nodes::OuterJoin).on(posts[:id].eq(comments[:post_id])) # LEFT OUTER JOIN `comments` `post_comments` ON `posts`.`id` = `post_comments`.`post_id`
    ```

    ### JOINS WITH SUBQUERIES
    ```ruby
    sub_join = posts.join(comments).on(posts[:id].eq(comments[:post_id])).project(:id, :user_id).as('commented_posts')
    users.join(
    sub_join
    ).on(sub_join[:user_id].eq(users[:id])) # SELECT * FROM `users` INNER JOIN (SELECT * FROM `posts` INNER JOIN `comments` ON `users`.`id` = `comments`.`post_id`) commented_posts ON `commented_posts`.`user_id` = `users`.`id`
    ```

    More examples: https://github.com/rails/rails/blob/master/activerecord/test/cases/arel/select_manager_test.rb#L533

    ## GROUP BY
    ```ruby
    users.group(users[:id], users[:name]) # GROUP BY `users`.`id`, `users`.`name`
    ```

    ## Limit / Skip
    ```ruby
    users.take(10) # SELECT * FROM `users` LIMIT 10
    users.skip(5) # SELECT * FROM `users` OFFSET 5
    ```

    ## Order
    ```ruby
    users[:id].desc # `users`.`id` DESC
    order(users[:id].desc).to_sql # SELECT FROM `users` ORDER BY `users`.`id` DESC
    ```

    ## Window functions

    ```ruby
    window = Arel::Nodes::Window.new.order(users[:name])
    users[:id].count.over(window) # COUNT(`users`.`id`) OVER (ORDER BY `users`.`name`)

    # Partition
    window = Arel::Nodes::Window.new.partition(users[:name])
    users[:id].count.over(window) # COUNT(`users`.`id`) OVER (PARTITION BY `users`.`name`)
    ```
    More examples here: https://github.com/rails/rails/blob/master/activerecord/test/cases/arel/select_manager_test.rb#L728


    ## Loooots of examples directly in the tests:

    https://github.com/rails/rails/blob/master/activerecord/test/cases/arel/select_manager_test.rb