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