-
Notifications
You must be signed in to change notification settings - Fork 0
SELECT
Alex edited this page Jul 29, 2018
·
3 revisions
Possible structures:
- columns
- sub querry
- SQL operator
-
Array
with combination of possible structures.
# String
constructor.select "name, email"
# Symbol
constructor.select :birthdate
# Array as columns
constructor.select [:phone, 'role']
# Array as SQL function
constructor.select [:_nullif, :row, :value]
# Hash with column alias(`AS`) as key and any available for `select` value
constructor.select _missed_calls_count:
{ select: [:_count, [:_nullif, :connected, :true]],
from: [:calls, :c],
where: ['c.client_id = u.id',
['direction = ?', 0]]}
# Hash with table alias as key and any available for `select` values
constructor.select m: [:common_rating, :work_rating, { _master_id: :id }]
# You can take a look of resulted data structure. In future, perhaps, Constructor will be more complicated and it will merge hashes...
constructor.structure
# => { select: ['name, email', :birthdate, :phone, 'role',
# [:_nullif, :row, :value],
# { _missed_calls_count: { select: [:_count, [:_nullif, :connected, :true]],
# from: [:calls, :c],
# where: ['c.client_id = u.id',
# ['direction = ?', 0]]} }] },
# { m: [:common_rating, :work_rating, { _master_id: :id }] }
constructor.build_sql
# => ['SELECT name, email, birthdate, phone, role, NULLIF(row, value), m.common_rating, m.work_rating, m.id AS master_id,
# (SELECT COUNT(NULLIF(connected, TRUE))
# FROM calls c
# WHERE c.client_id = u.id AND direction = $1) AS missed_calls_count',
# 0]
To specify distinct select you should add to your data structure :distinct
value:
true
-
columns
Or withConstructor
instance methods: -
.distinct
- distinct structure - optional
-
.distinct_select
- select structure
- distinct structure - optional
In constructor methods
distinct: true
passed by default
# as data
distinct: true, select: true
# => 'SELECT DISTINCT * '
# OR via constructor
constructor.distinct_select([:id, :name]).build_sql # => 'SELECT DISTINCT id, name'
# OR
constructor.distinct_select([:id, :name], :phone).build_sql # => 'SELECT DISTINCT ON(phone) id, name '