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