postgres_ext/test/queries/common_table_expression_test.rb at fca0acd1e2f10daee9319a3f17f3fb792f9ebb1a · DavyJonesLocker/postgres_ext · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
require 'test_helper'

describe 'Common Table Expression queries' do
  describe '.with(common_table_expression_hash)' do
    it 'generates an expression with the CTE' do
      query = Person.with(lucky_number_seven: Person.where(lucky_number: 7)).joins('JOIN lucky_number_seven ON lucky_number_seven.id = people.id')
      query.to_sql.must_match(/WITH "lucky_number_seven" AS \(SELECT "people".* FROM "people"(\s+)WHERE "people"."lucky_number" = 7\) SELECT "people".* FROM "people" JOIN lucky_number_seven ON lucky_number_seven.id = people.id/)
    end

    it 'generates an expression with multiple CTEs' do
      query = Person.with(lucky_number_seven: Person.where(lucky_number: 7), lucky_number_three: Person.where(lucky_number: 3)).joins('JOIN lucky_number_seven ON lucky_number_seven.id = people.id').joins('JOIN lucky_number_three ON lucky_number_three.id = people.id')
      query.to_sql.must_match(/WITH "lucky_number_seven" AS \(SELECT "people".* FROM "people"(\s+)WHERE "people"."lucky_number" = 7\), "lucky_number_three" AS \(SELECT "people".* FROM "people"(\s+)WHERE "people"."lucky_number" = 3\) SELECT "people".* FROM "people" JOIN lucky_number_seven ON lucky_number_seven.id = people.id JOIN lucky_number_three ON lucky_number_three.id = people.id/)
    end

    it 'generates an expression with multiple with calls' do
      query = Person.with(lucky_number_seven: Person.where(lucky_number: 7)).with(lucky_number_three: Person.where(lucky_number: 3)).joins('JOIN lucky_number_seven ON lucky_number_seven.id = people.id').joins('JOIN lucky_number_three ON lucky_number_three.id = people.id')
      query.to_sql.must_match(/WITH "lucky_number_seven" AS \(SELECT "people".* FROM "people"(\s+)WHERE "people"."lucky_number" = 7\), "lucky_number_three" AS \(SELECT "people".* FROM "people"(\s+)WHERE "people"."lucky_number" = 3\) SELECT "people".* FROM "people" JOIN lucky_number_seven ON lucky_number_seven.id = people.id JOIN lucky_number_three ON lucky_number_three.id = people.id/)
    end

    it 'generates an expression with recursive' do
      query = Person.with.recursive(lucky_number_seven: Person.where(lucky_number: 7)).joins('JOIN lucky_number_seven ON lucky_number_seven.id = people.id')
      query.to_sql.must_match(/WITH RECURSIVE "lucky_number_seven" AS \(SELECT "people".* FROM "people"(\s+)WHERE "people"."lucky_number" = 7\) SELECT "people".* FROM "people" JOIN lucky_number_seven ON lucky_number_seven.id = people.id/)
    end

    it 'preserves recursive expression during merges' do
      merge_recursive_in   = Tag.all.merge(Tag.recursive)
      merge_recursive_in.to_sql.must_match(/WITH RECURSIVE "recursive" AS \(SELECT "tags".* FROM "tags"(\s+)WHERE "tags"."tag" = 'tag'\) SELECT "tags".* FROM "tags"/)

      merge_into_recursive = Tag.recursive.merge(Tag.all)
      merge_into_recursive.to_sql.must_match(/WITH RECURSIVE "recursive" AS \(SELECT "tags".* FROM "tags"(\s+)WHERE "tags"."tag" = 'tag'\) SELECT "tags".* FROM "tags"/)
    end

    it 'accepts Arel::SelectMangers' do
      arel_table = Arel::Table.new 'test'
      arel_manager = arel_table.project arel_table[:foo]

      query = Person.with(testing: arel_manager)
      query.to_sql.must_equal 'WITH "testing" AS (SELECT "test"."foo" FROM "test") SELECT "people".* FROM "people"'
    end
  end

  describe '.with(common_table_exression_arel_nodes_as)' do
    it 'generates an expression with the CTE' do
      table_def = Arel::Nodes::SqlLiteral.new("update_cte(id, new_lucky)")
      new_values = "(1,12),(2,3),(3,8)"

      select = Arel::Nodes::SqlLiteral.new( "(VALUES #{new_values})" )
      with = Arel::Nodes::As.new(table_def, select)

      regex_safe = new_values.gsub("(","\\(").gsub(")","\\)")

      query = Person.with(with).joins('JOIN update_cte ON update_cte.id = people.id')
      query.to_sql.must_match(/WITH update_cte\(id, new_lucky\) AS \(VALUES #{regex_safe}\) SELECT \"people\".* FROM \"people\" JOIN update_cte ON update_cte.id = people.id/)
    end

    it 'generates an expression mixed with multiple with calls' do
      table_def = Arel::Nodes::SqlLiteral.new("update_cte(id, new_lucky)")
      new_values = "(1,12),(2,3),(3,8)"

      select = Arel::Nodes::SqlLiteral.new( "(VALUES #{new_values})" )
      with = Arel::Nodes::As.new(table_def, select)

      regex_safe = new_values.gsub("(","\\(").gsub(")","\\)")

      query = Person.with(with).with(lucky_number_seven: Person.where(lucky_number: 7)).joins('JOIN update_cte ON update_cte.id = people.id').joins('JOIN lucky_number_seven ON lucky_number_seven.id = people.id')
      query.to_sql.must_match(/WITH update_cte\(id, new_lucky\) AS \(VALUES #{regex_safe}\), "lucky_number_seven" AS \(SELECT "people".* FROM "people"(\s+)WHERE "people"."lucky_number" = 7\) SELECT \"people\".* FROM \"people\" JOIN update_cte ON update_cte.id = people.id JOIN lucky_number_seven ON lucky_number_seven.id = people.id/)
    end
  end

  describe '.from_cte(common_table_expression_hash)' do
    it 'generates an expression with the CTE as the main table' do
      query = Person.from_cte('lucky_number_seven', Person.where(lucky_number: 7)).where(id: 5)
      query.to_sql.must_match(/WITH "lucky_number_seven" AS \(SELECT "people".* FROM "people"(\s+)WHERE "people"."lucky_number" = 7\) SELECT "lucky_number_seven".* FROM "lucky_number_seven"(\s+)WHERE "lucky_number_seven"."id" = 5/)
    end

    it 'returns instances of the model' do
      3.times { Person.create! lucky_number: 7 }
      3.times { Person.create! lucky_number: 3 }
      people = Person.from_cte('lucky_number_seven', Person.where(lucky_number: 7))

      people.count.must_equal 3
      people.first.lucky_number.must_equal 7
    end

    it 'responds to table_name' do
      people = Person.from_cte('lucky_number_seven', Person.where(lucky_number: 7))

      people.model_name.must_equal 'Person'
    end
  end

  describe '.merge(Model.with(common_table_expression_hash))' do
    it 'keeps the CTE in the merged request' do
      query = Person.all.merge(Person.with(lucky_number_seven: Person.where(lucky_number: 7))).joins('JOIN lucky_number_seven ON lucky_number_seven.id = people.id')
      query.to_sql.must_match(/WITH "lucky_number_seven" AS \(SELECT "people".* FROM "people"(\s+)WHERE "people"."lucky_number" = 7\) SELECT "people".* FROM "people" JOIN lucky_number_seven ON lucky_number_seven.id = people.id/)
    end
  end

end