Arel Sample (5)

I continue to write sample code for Arel.

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

    q = i.
      project(Arel.sql("DISTINCT i.*"))

#    We cannot write as following. It make string 'DISTINCT'.
#    q = i.
#      project(Arel.sql("DISTINCT i.*")).
#      distinct

    q.to_sql.should eq "SELECT DISTINCT i.*" +
      " FROM `items` `i` "
  end

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

    # Arel does not support case expression.
    # So we have to write case expression using Arel.sql.
    q = od.
      project(od[:order_no],
        od[:item_no],
        Arel.sql("CASE" +
          " WHEN od.quantity > 20 THEN 'more than 20'" +
          " WHEN od.quantity > 10 THEN 'more than 10 and less then or equal to 20'" +
          " ELSE 'less than or equal to 10'" +
          " END").as("qty_desc"))

    q.to_sql.should eq "SELECT `od`.`order_no`," +
      " `od`.`item_no`," +
      " CASE" +
        " WHEN od.quantity > 20 THEN 'more than 20'" +
        " WHEN od.quantity > 10 THEN 'more than 10 and less then or equal to 20'" +
        " ELSE 'less than or equal to 10'" +
        " END AS qty_desc" +
      " FROM `order_details` `od` "
  end

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

    q = od.
      project(od[:quantity] + Arel.sql("10"),
        od[:quantity] - Arel.sql("10"),
        od[:quantity] * Arel.sql("2"),
        od[:quantity] / Arel.sql("2"))

    q.to_sql.should eq "SELECT (`od`.`quantity` + 10)," +
      " (`od`.`quantity` - 10)," +
      " `od`.`quantity` * 2," +
      " `od`.`quantity` / 2" +
      " FROM `order_details` `od` "
  end

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

    # This is seem to be so compilcated. Does exist simpler descriptions?
    q = od.
      project(Arel.sql((od[:quantity] + Arel.sql("10")).to_sql).as("qty_plus_10"),
        Arel.sql((od[:quantity] - Arel.sql("10")).to_sql).as("qty_minus_10"),
        Arel.sql((od[:quantity] * Arel.sql("2")).to_sql).as("qty_twice"),
        Arel.sql((od[:quantity] / Arel.sql("2")).to_sql).as("qty_half"))

    q.to_sql.should eq "SELECT (`od`.`quantity` + 10) AS qty_plus_10," +
      " (`od`.`quantity` - 10) AS qty_minus_10," +
      " `od`.`quantity` * 2 AS qty_twice," +
      " `od`.`quantity` / 2 AS qty_half" +
      " FROM `order_details` `od` "
  end

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

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

    q1 = od.
      group(od[:order_no]).
      project(od[:order_no],
        od[:quantity].sum.as("qty"))

    odsum = q1.as("od_sum")

    q = o.
      join(odsum).on(odsum[:order_no].eq(o[:order_no])).
      project(o[:order_no],
        odsum[:qty])

    q.to_sql.should eq "SELECT `o`.`order_no`," +
      " od_sum.`qty`" +
      " FROM `orders` `o`" +
      " INNER JOIN (SELECT `od`.`order_no`," +
        " SUM(`od`.`quantity`) AS qty" +
        " FROM `order_details` `od` " +
        " GROUP BY `od`.`order_no`) od_sum" +
      " ON od_sum.`order_no` = `o`.`order_no`"
  end