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