gistfile1.md

Made up of managers, Abstract Syntax Tree AST (nodes), visitors

• Managers retain lists of nodes

  • Select, update, insert, delete

• Managers provide access to visitors, which visit nodes and construct sql

• AST defines various portions of the query

  • select, from/join, where, group, order, limit, offset

• @user = User.arel_table@

• @query = user.project('*')@

  • @query.to_sql@ == @SELECT * FROM "users"@

• @query = user.project(user[:id])@

  • @query.to_sql@ == @SELECT "users"."id" FROM "users"@

• @arel_table[:foo].@ -- return nodes, which can be converted to sql or passed to AR

  • AR#where, AR#having
    • @not_eq@ (i != n), @not_eq_any@ (i != n OR i != o), @not_eq_all@ (i != n AND i != o)
    • @eq@ (i = n), @eq_any@ (i = n OR i = o), @eq_all@ (i = n AND i = o)
    • @in@ (i IN (...)), @in_any@ (i IN (...) OR i IN (...)), @in_all@ (i IN (...) AND i IN (...))
    • @not_in@ (i NOT IN (...)), @not_in_any@ (i NOT IN (...) OR i NOT IN (...)), @not_in_all@ (i NOT IN (...) AND i NOT IN (...))
    • @matches@ (i ILIKE 'n'), @matches_any@ (i ILIKE 'n' OR i ILIKE 'o'), @matches_all@ (i ILIKE 'n' AND i ILIKE 'o')
    • @does_not_match@ (i NOT ILIKE 'n'), @does_not_match_any@ (i NOT ILIKE 'n' OR i NOT ILIKE 'o'), @does_not_match_all@ (i NOT ILIKE 'n' AND i NOT ILIKE 'o')
    • @gteq@ (i >= n), @gteq_any@ (i >= n OR i >= o), @gteq_all@ (i >= n AND i >= o)
    • @gt@ (i > n), @gt_any@ (i > n OR i > o), @gt_all@ (i > n AND i > o)
    • @lteq@ (i <= n), @lteq_any@ (i <= n OR i <= o), @lteq_all@ (i <= n AND i <= o)
    • @lt@ (i < n), @lt_any@ (i < n OR i < o), @lt_all@ (i < n AND i < o)
  • AR#where, AR#select, or AR#having
    • (maximum|minimum|sum|count|average) .as "string"
      • @User.group(u[:id]).having(u[:id].count.gt(100)).to_sql@
      • @SELECT "users".* FROM "users" GROUP BY "users"."id" HAVING COUNT("users"."id") > 100@
    • @lower@ (LOWER(i))
    • @@ (i * j) @ as@ (i * j AS foo)
    • @/@ (i / j) @/ as@ (i / j AS foo)
    • no bitwise operators
    • @+@ (i + j) can not alias, grouping prevents it
    • @-@ (i - j) can not alias, grouping prevents it
  • AR#order
    • @asc@ (i ASC)
    • @desc@ (i DESC)
    • @to_sql@

• @arel_table@

  • @columns@ returns array of attributes
  • @primary_key@ returns attribute, @primary_key.name@ returns field name
  • @join@ join statement -- returns select manager
    • @b=Brand.arel_table; s=SuperCampaign.arel_table; b.join(s).on(s[:brand_id].eq(b[:id]))@
  • @grouping@ group nodes/predicates
    • @User.where(u.grouping(u[:id].gt(10).and(u[:username].matches("foo%"))).or(u[:email].eq("a@b.com"))).to_sql@
      • @SELECT "users".* FROM "users" WHERE ((("users"."id" > 10 AND "users"."username" ILIKE 'foo%') OR "users"."email" = 'a@b.com'))@

• @select managers@

  • @join_sql@
  • @to_sql@
  • @where_sql@
  • @as@ alias the query as a subselect

• ActiveRecord

  • Functions
    • @User.select(Arel::Nodes::NamedFunction.new(:coalesce, [u[:id], 0], ALIAS)))@
      • @SELECT coalesce("users"."id", 0) FROM "users"@
      • @SELECT coalesce("users"."id", 0) AS foo FROM "users"@
  • Subqueries and unions
    • @User.select(u[:id]).as("booger").to_sql@
      • @(SELECT "users"."id" FROM "users" ) booger@
    • @User.select('*').from(User.select(u[:id]).as("booger")).to_sql@
      • @SELECT * FROM (SELECT "users"."id" FROM "users" ) booger@
    • @User.select(u[:id]).union(Brand.select(b[:id])).to_sql@
      • @( SELECT "users"."id" FROM "users" UNION SELECT "brands"."id" FROM "brands" WHERE (brands.deleted_at IS NULL) )@