Class: Json2sql::SelectModel

Inherits:
Object
  • Object
show all
Defined in:
lib/json2sql/select_model.rb

Overview

Builds a SELECT SQL statement for a single table.

Input Hash keys (all optional):

"columns"  => ["id", "name", ...]   – columns to SELECT
"where"    => { "and" => {...} }    – WHERE conditions (see WhereModel)
"and"      => {...}                 – shorthand for top-level AND WHERE
"or"       => {...}                 – shorthand for top-level OR WHERE
"order"    => { "col" => "asc" }   – ORDER BY
"limit"    => 10                   – LIMIT
"offset"   => 20                   – OFFSET
"options"  => ["total"]            – wrap response with data/total JSON
"children" => { "table" => {...} } – nested child arrays
"parents"  => { "table" => {...} } – nested parent objects

Instance Method Summary collapse

Constructor Details

#initialize(sql, table, relation) ⇒ SelectModel

Returns a new instance of SelectModel.



19
20
21
22
23
24
25
26
 
# File 'lib/json2sql/select_model.rb', line 19

def initialize(sql, table, relation)

  @sql = sql

  @table = table.to_s

  @relation = relation
end

Instance Method Details

#build_query_array(params) ⇒ Object

SELECT JSON_ARRAYAGG(JSON_OBJECT(…)) AS ‘table` FROM LATERAL (SELECT * FROM `table` WHERE … ORDER … LIMIT …) AS `table`



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
 
# File 'lib/json2sql/select_model.rb', line 67

def build_query_array(params)

  @sep = false

  @sql << "SELECT JSON_ARRAYAGG(JSON_OBJECT("

  build_columns_json(params)
  build_columns_array(params)
  build_columns_object(params)

  @sql << ")) AS "

  @sql << Sanitizer.keyword_wrap(@table)

  @sql << " FROM LATERAL (SELECT * FROM "

  @sql << Sanitizer.keyword_wrap(@table)

  WhereModel.new(@sql, @table, @relation).build(params)

  build_order(params)
  build_limit(params)
  build_offset(params)

  @sql << ") AS "

  @sql << Sanitizer.keyword_wrap(@table)
end

#build_query_count(params) ⇒ Object

SELECT COUNT(*) AS ‘table` FROM `table` WHERE …



30
31
32
33
34
35
36
37
38
39
40
41
 
# File 'lib/json2sql/select_model.rb', line 30

def build_query_count(params)

  @sql << "SELECT COUNT(*) AS "
  
  @sql << Sanitizer.keyword_wrap(@table)
  
  @sql << " FROM "
        
  @sql << Sanitizer.keyword_wrap(@table)

  WhereModel.new(@sql, @table, @relation).build(params)
end

#build_query_default(params) ⇒ Object

Plain SELECT col1, col2 FROM ‘table` WHERE … ORDER BY … LIMIT … OFFSET …



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/json2sql/select_model.rb', line 45

def build_query_default(params)

  @sep = false

  @sql << "SELECT "

  build_columns_default(params)

  @sql << " FROM "

  @sql << Sanitizer.keyword_wrap(@table)

  WhereModel.new(@sql, @table, @relation).build(params)

  build_order(params)      
  build_limit(params)
  build_offset(params)
end

#build_query_object(params) ⇒ Object

SELECT JSON_OBJECT(…) AS ‘table` FROM LATERAL (SELECT * FROM `table` WHERE …) AS `table`



99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
 
# File 'lib/json2sql/select_model.rb', line 99

def build_query_object(params)

  @sep = false

  @sql << "SELECT JSON_OBJECT("

  build_columns_json(params)
  build_columns_array(params)
  build_columns_object(params)

  @sql << ") AS "

  @sql << Sanitizer.keyword_wrap(@table)

  @sql << " FROM LATERAL (SELECT * FROM "

  @sql << Sanitizer.keyword_wrap(@table)

  WhereModel.new(@sql, @table, @relation).build(params)

  build_order(params)
  build_limit(params)
  build_offset(params)

  @sql << ") AS "

  @sql << Sanitizer.keyword_wrap(@table)
end

#build_query_options(params) ⇒ Object

Smart dispatcher:

- no options → build_query_array
- options includes "total" → wraps with JSON_OBJECT('data', ..., 'total', COUNT(*))



132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
 
# File 'lib/json2sql/select_model.rb', line 132

def build_query_options(params)

  options = params["options"]

  unless options.is_a?(Array) && !options.empty?

    build_query_array(params)

    return
  end

  total = options.include?("total")

  @sql << "SELECT JSON_OBJECT('data', ("

  build_query_array(params)

  @sql << ")"

  if total

    @sql << ", 'total', ("

    build_query_count(params)

    @sql << ")"
  end

  @sql << ")"
end