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