Arel Sample (4)

I continue to write sample code for Arel.

  it "exists_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q1 = od.
      where(od[:order_no].eq(o[:order_no])).
      where(od[:quantity].gt(10)).
      project(Arel.sql("1"))
        
    q = o.
      where(q1.exists).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE EXISTS (SELECT 1" +
        " FROM `order_details` `od` " +
        " WHERE `od`.`order_no` = `o`.`order_no`" +
        " AND `od`.`quantity` > 10)"
  end

  it "not_exists_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q1 = od.
      where(od[:order_no].eq(o[:order_no])).
      where(od[:quantity].gt(10)).
      project(Arel.sql("1"))
        
    # 'not_exists' does not exists. We use 'not' for 'exists'.
    q = o.
      where(q1.exists.not).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE NOT (EXISTS (SELECT 1" +
        " FROM `order_details` `od` " +
        " WHERE `od`.`order_no` = `o`.`order_no`" +
        " AND `od`.`quantity` > 10))"
  end

  it "between_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    date = Date.new(2013,7,24)
    # Arel does not support BETWEEN expression directly.
    # So we have to use Arel.sql. 
    # We can use 'ActiveRecord::Base.sanitize' method for sanitize data.
    # The Model class, for example Item, can be used instead of ActiveRecord::Base.
    # ex.) Item.sanitize(date)
    q = i.
      where(Arel.sql("#{ActiveRecord::Base.sanitize(date)} BETWEEN i.start_date AND i.end_date")).
      project(i[Arel.star])

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE '2013-07-24' BETWEEN i.start_date AND i.end_date"
  end

  it "max_min_avg_alias_sample1" do
    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q = od.
      group(od[:order_no]).
      project(od[:order_no],
        od[:quantity].maximum.as("max_qty"),
        od[:quantity].minimum.as("min_qty"),
        od[:quantity].average.as("avg_qty"))

    q.to_sql.should eq "SELECT `od`.`order_no`," +
      " MAX(`od`.`quantity`) AS max_qty," +
      " MIN(`od`.`quantity`) AS min_qty," +
      " AVG(`od`.`quantity`) AS avg_qty" +
      " FROM `order_details` `od` " +
      " GROUP BY `od`.`order_no`"
  end

  it "skip_take_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    q = o.
      project(o[Arel.star]).
      skip(30).take(10)

    q.to_sql.should eq "SELECT  `o`.*" +
      " FROM `orders` `o` " +
      " LIMIT 10 OFFSET 30"
  end

  it "in_subquery_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    i2 = Arel::Table.new(:items)
    i2.table_alias = "i2"

    q1 = i2.
      where(i2[:item_no].eq(i[:item_no])).
      where(i2[:start_date].lteq(Date.new(2013,7,24))).
      project(i2[:start_date].maximum.as("max_start_date"))

    # This case can be written as 'start_date = (SELECT MAX(start_date)...) in SQL'
    # But Arel does not support eq(q1) and we write the statement using 'in'.
    q = i.
      where(i[:start_date].in(q1)).
      project(i[Arel.star])

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE `i`.`start_date` IN (SELECT MAX(`i2`.`start_date`) AS max_start_date" +
        " FROM `items` `i2` " +
        " WHERE `i2`.`item_no` = `i`.`item_no`" +
        " AND `i2`.`start_date` <= '2013-07-24')"
  end

Arel Sample (3)

I continue to write sample code for Arel.

  it "group_by_having_count_sample1" do
    o = Arel::Table.new(:order)
    o.table_alias = "o"

    q = o.
      group(o[:order_date]).
      having(Arel.star.count.gt(5)).
      project(o[:order_date],
        Arel.star.count)

    q.to_sql.should eq "SELECT `o`.`order_date`," +
      " COUNT(*)" +
      " FROM `order` `o` " +
      " GROUP BY `o`.`order_date`" +
      " HAVING COUNT(*) > 5"
  end

  it "group_by_having_sum_sample1" do
    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q = od.
      group(od[:item_no]).
      having(Arel.sql("SUM(od.quantity)").gt(5)).
      project(od[:item_no],
        Arel.star.count,
        od[:quantity].sum)

    q.to_sql.should eq "SELECT `od`.`item_no`," +
      " COUNT(*)," +
      " SUM(`od`.`quantity`) AS sum_id" +
      " FROM `order_details` `od` " +
      " GROUP BY `od`.`item_no`" +
      " HAVING SUM(od.quantity) > 5"

=begin *.sum cannot be used in having clause because it is aliased like 'AS sum_id'
    q = od.
      group(od[:item_no]).
      having(od[:quantity].sum.gt(5)).
      project(od[:item_no],
        Arel.star.count,
        od[:quantity].sum)

    q.to_sql.should eq "SELECT `od`.`item_no`," +
      " COUNT(*)," +
      " SUM(`od`.`quantity`) AS sum_id" +
      " FROM `order_details` `od` " +
      " GROUP BY `od`.`item_no`" +
      " HAVING SUM(`od`.`quantity`) AS sum_id > 5"
=end
  end

  it "eq_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000101", "000102", "000103"]
    # We can use 'eq_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_no].eq_any(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` = '000101'" +
        " OR `o`.`order_no` = '000102'" +
        " OR `o`.`order_no` = '000103')"
  end

  it "eq_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000100", "000100", "000100"]
    # We can use 'eq_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_no].eq_all(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` = '000100'" +
        " AND `o`.`order_no` = '000100'" +
        " AND `o`.`order_no` = '000100')"
  end

  it "not_eq_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000100", "000100", "000100"]
    # We can use 'not_eq_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_no].not_eq_any(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` != '000100'" +
        " OR `o`.`order_no` != '000100'" +
        " OR `o`.`order_no` != '000100')"
  end

  it "not_eq_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000101", "000102", "000103"]
    # We can use 'not_eq_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_no].not_eq_all(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` != '000101'" +
        " AND `o`.`order_no` != '000102'" +
        " AND `o`.`order_no` != '000103')"
  end

  it "gt_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'gt_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_date].gt_any(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` > '2013-07-24'" +
        " OR `o`.`order_date` > '2013-07-25'" +
        " OR `o`.`order_date` > '2013-07-26')"
  end

  it "gt_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'gt_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_date].gt_all(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` > '2013-07-24'" +
        " AND `o`.`order_date` > '2013-07-25'" +
        " AND `o`.`order_date` > '2013-07-26')"
  end

  it "lt_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'lt_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_date].lt_any(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` < '2013-07-24'" +
        " OR `o`.`order_date` < '2013-07-25'" +
        " OR `o`.`order_date` < '2013-07-26')"
  end

  it "lt_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'lt_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_date].lt_all(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` < '2013-07-24'" +
        " AND `o`.`order_date` < '2013-07-25'" +
        " AND `o`.`order_date` < '2013-07-26')"
  end

  it "gteq_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'gteq_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_date].gteq_any(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` >= '2013-07-24'" +
        " OR `o`.`order_date` >= '2013-07-25'" +
        " OR `o`.`order_date` >= '2013-07-26')"
  end

  it "gteq_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'gteq_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_date].gteq_all(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` >= '2013-07-24'" +
        " AND `o`.`order_date` >= '2013-07-25'" +
        " AND `o`.`order_date` >= '2013-07-26')"
  end

  it "lteq_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'lteq_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_date].lteq_any(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` <= '2013-07-24'" +
        " OR `o`.`order_date` <= '2013-07-25'" +
        " OR `o`.`order_date` <= '2013-07-26')"
  end

  it "lteq_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    date_list = [Date.new(2013,7,24),
      Date.new(2013,7,25),
      Date.new(2013,7,26)]
    # We can use 'lteq_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_date].lteq_all(date_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_date` <= '2013-07-24'" +
        " AND `o`.`order_date` <= '2013-07-25'" +
        " AND `o`.`order_date` <= '2013-07-26')"
  end

  it "like_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["%101%", "%102%", "%103%"]
    # We can use 'matches_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_no].matches_any(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` LIKE '%101%'" +
        " OR `o`.`order_no` LIKE '%102%'" +
        " OR `o`.`order_no` LIKE '%103%')"
  end

  it "like_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["%10%", "%", "%20%"]
    # We can use 'matches_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_no].matches_all(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` LIKE '%10%'" +
        " AND `o`.`order_no` LIKE '%'" +
        " AND `o`.`order_no` LIKE '%20%')"
  end

  it "in_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = [ ["000100", "000101"],
      ["000200", "000201"],
      ["000300", "000301"] ]
    # We can use 'in_any' for 'OR'-joined conditions
    q = o.
      where(o[:order_no].in_any(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` IN ('000100', '000101')" +
        " OR `o`.`order_no` IN ('000200', '000201')" +
        " OR `o`.`order_no` IN ('000300', '000301'))"
  end

  it "in_sample3" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = [ ["000100", "000101"],
      ["000100", "000102"],
      ["000100", "000103"] ]
    # We can use 'in_all' for 'AND'-joined conditions
    q = o.
      where(o[:order_no].in_all(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE (`o`.`order_no` IN ('000100', '000101')" +
        " AND `o`.`order_no` IN ('000100', '000102')" +
        " AND `o`.`order_no` IN ('000100', '000103'))"
  end

Arel Sample (2)

I continue to write sample code for Arel.

  it "order_by_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    q = o.
      project(o[Arel.star])
      .order(o[:order_date].desc,
        o[:order_no].asc)

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " ORDER BY `o`.`order_date` DESC," +
      " `o`.`order_no` ASC"
  end

  it "not_eq_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    q = i.
      where(i[:item_no].not_eq("10000001")).
      project(i[Arel.star])

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE `i`.`item_no` != '10000001'"
  end

  it "gteq_lteq_and_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    date = Date.new(2013,7.24)
    q = i.
      where(i[:start_date].lteq(date)).
      where(i[:end_date].gteq(date)).
      project(i[Arel.star])
    # where functions more than 1 make 'AND'

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE `i`.`start_date` <= '2013-07-01'" +
      " AND `i`.`end_date` >= '2013-07-01'"
  end

  it "gt_lt_or_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    date = Date.new(2013,7.24)
    q = i.
      where(i[:start_date].gt(date).
        or(i[:end_date].lt(date))).
      project(i[Arel.star])
    # where functions more than 1 make 'AND'

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE (`i`.`start_date` > '2013-07-01'" +
        " OR `i`.`end_date` < '2013-07-01')"
  end

  it "and_or_sample1" do
    i = Arel::Table.new(:items)
    i.table_alias = "i"

    dates = [ [Date.new(2013,7,24), Date.new(2013,7,28)],
      [Date.new(2013,8,1), Date.new(2013,8,5)],
      [Date.new(2013,8,10), Date.new(2013,8,12)] ]
    
    q = i
    dates.each do |pair|
      q = q.
        where(i[:start_date].gt(pair[0]).
          or(i[:start_date].lt(pair[1])))
    end
    q = q.project(i[Arel.star])

    q.to_sql.should eq "SELECT `i`.*" +
      " FROM `items` `i` " +
      " WHERE (`i`.`start_date` > '2013-07-24'" +
        " OR `i`.`start_date` < '2013-07-28')" +
      " AND (`i`.`start_date` > '2013-08-01'" +
        " OR `i`.`start_date` < '2013-08-05')" +
      " AND (`i`.`start_date` > '2013-08-10'" +
        " OR `i`.`start_date` < '2013-08-12')"
  end

  it "like_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no = "101"
    q = o.
      where(o[:order_no].matches("%#{order_no}%")).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE `o`.`order_no` LIKE '%101%'"
  end

  it "in_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000101", "000102", "000103"]
    q = o.
      where(o[:order_no].in(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +" WHERE `o`.`order_no` IN ('000101', '000102', '000103')"
  end

  it "not_like_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    # We can use 'does_not_match' for 'NOT LIKE' in SQL.
    order_no = "101"
    q = o.
      where(o[:order_no].does_not_match("%#{order_no}%")).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE `o`.`order_no` NOT LIKE '%101%'"
  end

  it "not_in_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    order_no_list = ["000101", "000102", "000103"]
    q = o.
      where(o[:order_no].not_in(order_no_list)).
      project(o[Arel.star])

    q.to_sql.should eq "SELECT `o`.*" +
      " FROM `orders` `o` " +
      " WHERE `o`.`order_no` NOT IN ('000101', '000102', '000103')"
  end

Arel Sample (1)

I study Ruby on Rails and I have to use Arel directly.
So I write some SQL patterns on this page for my memo.

The database schema is the following diagram.

I write using of Arel as Rspec.

  it "where_sample1" do
    order = Arel::Table.new(:orders)
    q = order.
      where(order[:order_date].eq(Date.new(2013, 7, 24))).
      project(Arel.star)

    q.to_sql.should eq "SELECT *" +
      " FROM `orders` " +
      " WHERE `orders`.`order_date` = '2013-07-24'"
  end

  it "table_alias_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    q = o.
      where(o[:order_date].eq(Date.new(2013, 7, 24))).
      project(Arel.star)

    q.to_sql.should eq "SELECT *" +
      " FROM `orders` `o` " +
      " WHERE `o`.`order_date` = '2013-07-24'"
  end

  it "join_sample1" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    q = o.
      join(od).on(od[:order_no].eq(o[:order_no])).
      where(o[:order_date].eq(Date.new(2013, 7, 24))).
      where(od[:detail_no].lteq(3)).
      project(o[Arel.star],
        od[Arel.star])

    q.to_sql.should eq "SELECT `o`.*," +
      " `od`.*" +
      " FROM `orders` `o`" +
      " INNER JOIN `order_details` `od`" +
      " ON `od`.`order_no` = `o`.`order_no`" +
      " WHERE `o`.`order_date` = '2013-07-24'" +
      " AND `od`.`detail_no` <= 3"
  end

  it "outer_join_sample1" do
    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    i = Arel::Table.new(:items)
    i.table_alias = "i"

    q = od.
      join(i, Arel::Nodes::OuterJoin).
      on(i[:item_no].eq(od[:item_no])).
      project(od[Arel.star],
        i[Arel.star])

    q.to_sql.should eq "SELECT `od`.*," +
      " `i`.*" +
      " FROM `order_details` `od`" +
      " LEFT OUTER JOIN `items` `i`" +
      " ON `i`.`item_no` = `od`.`item_no`"
  end

  it "join_sample2" do
    o = Arel::Table.new(:orders)
    o.table_alias = "o"

    od = Arel::Table.new(:order_details)
    od.table_alias = "od"

    i = Arel::Table.new(:items)
    i.table_alias = "i"

    q = o.
      join(od).on(od[:order_no].eq(o[:order_no])).
      join(i).on(i[:item_no].eq(od[:item_no])).
      project(o[Arel.star],
        od[Arel.star],
        i[Arel.star])

    q.to_sql.should eq "SELECT `o`.*," +
      " `od`.*," +
      " `i`.*" +
      " FROM `orders` `o`" +
      " INNER JOIN `order_details` `od`" +
      " ON `od`.`order_no` = `o`.`order_no`" +
      " INNER JOIN `items` `i`" +
      " ON `i`.`item_no` = `od`.`item_no`"
  end