Class: Lotus::Model::Adapters::Sql::Query

Inherits:
Object
  • Object
show all
Extended by:
Forwardable
Includes:
Enumerable
Defined in:
lib/lotus/model/adapters/sql/query.rb

Overview

Query the database with a powerful API.

All the methods are chainable, it allows advanced composition of SQL conditions.

This works as a lazy filtering mechanism: the records are fetched from the database only when needed.

It implements Ruby’s ‘Enumerable` and borrows some methods from `Array`. Expect a query to act like them.

Examples:


query.where(language: 'ruby')
     .and(framework: 'lotus')
     .reverse_order(:users_count).all

# the records are fetched only when we invoke #all

Since:

  • 0.1.0

Constant Summary collapse

OPERATORS_MAPPING =

This constant is part of a private API. You should avoid using this constant if possible, as it may be removed or be changed in the future.

Define negations for operators.

See Also:

Since:

  • 0.1.0

{
  where:   :exclude,
  exclude: :where
}.freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(collection, context = nil, &blk) ⇒ Lotus::Model::Adapters::Sql::Query

Initialize a query

Parameters:

Since:

  • 0.1.0



61
62
63
64
65
66
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 61

def initialize(collection, context = nil, &blk)
  @collection, @context = collection, context
  @conditions = []

  instance_eval(&blk) if block_given?
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(m, *args, &blk) ⇒ Object (protected)

This method is part of a private API. You should avoid using this method if possible, as it may be removed or be changed in the future.

Handles missing methods for query combinations

See Also:

  • Lotus::Model::Adapters:Sql::Query#apply

Since:

  • 0.1.0



670
671
672
673
674
675
676
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 670

def method_missing(m, *args, &blk)
  if @context.respond_to?(m)
    apply @context.public_send(m, *args, &blk)
  else
    super
  end
end

Instance Attribute Details

#conditions ⇒ Object (readonly)

This method is part of a private API. You should avoid using this method if possible, as it may be removed or be changed in the future.

Since:

  • 0.1.0



50
51
52
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 50

def conditions
  @conditions
end

Instance Method Details

#all ⇒ Array

Resolves the query by fetching records from the database and translating them into entities.

hitting the database for fetching records

Returns:

  • (Array) —

    a collection of entities

Raises:

Since:

  • 0.1.0



77
78
79
80
81
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 77

def all
  run.to_a
rescue Sequel::DatabaseError => e
  raise Lotus::Model::InvalidQueryError.new(e.message)
end

#average(column) ⇒ Numeric Also known as: avg

Returns the average of the values for the given column.

Examples:


query.average(:comments_count)

# => SELECT AVG(`comments_count`) FROM articles

Parameters:

  • column (Symbol) —

    the column name

Returns:

  • (Numeric)

Since:

  • 0.1.0



458
459
460
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 458

def average(column)
  run.avg(column)
end

#count ⇒ Fixnum

Returns a count of the records for the current conditions.

Examples:


query.where(author_id: 23).count # => 5

Returns:

  • (Fixnum)

Since:

  • 0.1.0



573
574
575
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 573

def count
  run.count
end

#exclude(condition = nil, &blk) ⇒ Object Also known as: not

Logical negation of a WHERE condition.

It accepts a ‘Hash` with only one pair. The key must be the name of the column expressed as a `Symbol`. The value is the one used by the SQL query

Examples:

Fixed value


query.exclude(language: 'java')

# => SELECT * FROM `projects` WHERE (`language` != 'java')

Array


query.exclude(id: [4, 9])

# => SELECT * FROM `articles` WHERE (`id` NOT IN (1, 3))

Range


query.exclude(year: 1900..1982)

# => SELECT * FROM `people` WHERE ((`year` < 1900) AND (`year` > 1982))

Multiple conditions


query.exclude(language: 'java')
     .exclude(company: 'enterprise')

# => SELECT * FROM `projects` WHERE (`language` != 'java') AND (`company` != 'enterprise')

Expressions


query.exclude{ age > 31 }

# => SELECT * FROM `users` WHERE (`age` <= 31)

Parameters:

  • condition (Hash) (defaults to: nil)

Returns:

  • self

Since:

  • 0.1.0



216
217
218
219
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 216

def exclude(condition = nil, &blk)
  _push_to_conditions(:exclude, condition || blk)
  self
end

#exist? ⇒ TrueClass, FalseClass

Checks if at least one record exists for the current conditions.

Examples:


query.where(author_id: 23).exists? # => true

Returns:

  • (TrueClass, FalseClass)

Since:

  • 0.1.0



560
561
562
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 560

def exist?
  !count.zero?
end

#group(*columns) ⇒ Object

Group by the specified columns.

Examples:

Single column


query.group(:name)

# => SELECT * FROM `people` GROUP BY `name`

Multiple columns


query.group(:name, :year)

# => SELECT * FROM `people` GROUP BY `name`, `year`

Parameters:

  • columns (Array<Symbol>)

Returns:

  • self

Since:

  • 0.5.0



423
424
425
426
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 423

def group(*columns)
  conditions.push([:group, *columns])
  self
end

#interval(column) ⇒ Numeric

Returns the difference between the MAX and MIN for the given column.

Examples:


query.interval(:comments_count)

# => SELECT (MAX(`comments_count`) - MIN(`comments_count`)) FROM articles

Parameters:

  • column (Symbol) —

    the column name

Returns:

  • (Numeric)

See Also:

Since:

  • 0.1.0



526
527
528
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 526

def interval(column)
  run.interval(column)
end

#join(collection, options = {}) ⇒ Object Also known as: inner_join

Specify an ‘INNER JOIN` clause.

Examples:


query.join(:users)

# => SELECT * FROM `posts` INNER JOIN `users` ON `posts`.`user_id` = `users`.`id`

Parameters:

  • collection (String)
  • options (Hash) (defaults to: {})
  • key (Hash) —

    a customizable set of options

  • foreign_key (Hash) —

    a customizable set of options

Returns:

  • self

Since:

  • 0.5.0



635
636
637
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 635

def join(collection, options = {})
  _join(collection, options.merge(join: :inner))
end

#left_join(collection, options = {}) ⇒ Object Also known as: left_outer_join

Specify a ‘LEFT JOIN` clause.

Examples:


query.left_join(:users)

# => SELECT * FROM `posts` LEFT JOIN `users` ON `posts`.`user_id` = `users`.`id`

Parameters:

  • collection (String)
  • options (Hash) (defaults to: {})
  • key (Hash) —

    a customizable set of options

  • foreign_key (Hash) —

    a customizable set of options

Returns:

  • self

Since:

  • 0.5.0



657
658
659
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 657

def left_join(collection, options = {})
  _join(collection, options.merge(join: :left))
end

#limit(number) ⇒ Object

Limit the number of records to return.

This operation is performed at the database level with ‘LIMIT`.

Examples:


query.limit(1)

# => SELECT * FROM `people` LIMIT 1

Parameters:

  • number (Fixnum)

Returns:

  • self

Since:

  • 0.1.0



264
265
266
267
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 264

def limit(number)
  conditions.push([:limit, number])
  self
end

#max(column) ⇒ Object

Returns the maximum value for the given column.

Examples:

With numeric type


query.max(:comments_count)

# => SELECT MAX(`comments_count`) FROM articles

With string type


query.max(:title)

# => SELECT MAX(`title`) FROM articles

Parameters:

  • column (Symbol) —

    the column name

Returns:

  • result

Since:

  • 0.1.0



483
484
485
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 483

def max(column)
  run.max(column)
end

#min(column) ⇒ Object

Returns the minimum value for the given column.

Examples:

With numeric type


query.min(:comments_count)

# => SELECT MIN(`comments_count`) FROM articles

With string type


query.min(:title)

# => SELECT MIN(`title`) FROM articles

Parameters:

  • column (Symbol) —

    the column name

Returns:

  • result

Since:

  • 0.1.0



506
507
508
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 506

def min(column)
  run.min(column)
end

#negate! ⇒ Object

Negates the current where/exclude conditions with the logical opposite operator.

All the other conditions will be ignored.

Examples:


query.where(language: 'java').negate!.all

# => SELECT * FROM `projects` WHERE (`language` != 'java')

See Also:

Since:

  • 0.1.0



593
594
595
596
597
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 593

def negate!
  conditions.map! do |(operator, condition)|
    [OPERATORS_MAPPING.fetch(operator) { operator }, condition]
  end
end

#offset(number) ⇒ Object

Specify an ‘OFFSET` clause.

Due to SQL syntax restriction, offset MUST be used with ‘#limit`.

Examples:


query.limit(1).offset(10)

# => SELECT * FROM `people` LIMIT 1 OFFSET 10

Parameters:

  • number (Fixnum)

Returns:

  • self

See Also:

Since:

  • 0.1.0



286
287
288
289
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 286

def offset(number)
  conditions.push([:offset, number])
  self
end

#or(condition = nil, &blk) ⇒ Object

Adds a SQL ‘OR` condition.

It accepts a ‘Hash` with only one pair. The key must be the name of the column expressed as a `Symbol`. The value is the one used by the SQL query

This condition will be ignored if not used with WHERE.

Examples:

Fixed value


query.where(language: 'ruby').or(framework: 'lotus')

# => SELECT * FROM `projects` WHERE ((`language` = 'ruby') OR (`framework` = 'lotus'))

Array


query.where(id: 1).or(author_id: [15, 23])

# => SELECT * FROM `articles` WHERE ((`id` = 1) OR (`author_id` IN (15, 23)))

Range


query.where(country: 'italy').or(year: 1900..1982)

# => SELECT * FROM `people` WHERE ((`country` = 'italy') OR ((`year` >= 1900) AND (`year` <= 1982)))

Expressions


query.where(name: 'John').or{ age > 31 }

# => SELECT * FROM `users` WHERE ((`name` = 'John') OR (`age` < 32))

Parameters:

  • condition (Hash) (defaults to: nil)

Returns:

  • self

Since:

  • 0.1.0



169
170
171
172
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 169

def or(condition = nil, &blk)
  _push_to_conditions(:or, condition || blk)
  self
end

#order(*columns) ⇒ Object Also known as: asc

Specify the ascending order of the records, sorted by the given columns.

Examples:

Single column


query.order(:name)

# => SELECT * FROM `people` ORDER BY (`name`)

Multiple columns


query.order(:name, :year)

# => SELECT * FROM `people` ORDER BY `name`, `year`

Multiple invokations


query.order(:name).order(:year)

# => SELECT * FROM `people` ORDER BY `name`, `year`

Parameters:

  • columns (Array<Symbol>) —

    the column names

Returns:

  • self

See Also:

Since:

  • 0.1.0



319
320
321
322
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 319

def order(*columns)
  conditions.push([_order_operator, *columns])
  self
end

#range(column) ⇒ Range

Returns a range of values between the MAX and the MIN for the given column.

Examples:


query.range(:comments_count)

# => SELECT MAX(`comments_count`) AS v1, MIN(`comments_count`) AS v2 FROM articles

Parameters:

  • column (Symbol) —

    the column name

Returns:

  • (Range)

See Also:

Since:

  • 0.1.0



547
548
549
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 547

def range(column)
  run.range(column)
end

#reverse_order(*columns) ⇒ Object Also known as: desc

Specify the descending order of the records, sorted by the given columns.

Examples:

Single column


query.reverse_order(:name)

# => SELECT * FROM `people` ORDER BY (`name`) DESC

Multiple columns


query.reverse_order(:name, :year)

# => SELECT * FROM `people` ORDER BY `name`, `year` DESC

Multiple invokations


query.reverse_order(:name).reverse_order(:year)

# => SELECT * FROM `people` ORDER BY `name`, `year` DESC

Parameters:

  • columns (Array<Symbol>) —

    the column names

Returns:

  • self

See Also:

Since:

  • 0.3.1



377
378
379
380
381
382
383
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 377

def reverse_order(*columns)
  Array(columns).each do |column|
    conditions.push([_order_operator, Sequel.desc(column)])
  end

  self
end

#scoped ⇒ Lotus::Model::Adapters::Sql::Collection Also known as: run

Apply all the conditions and returns a filtered collection.

This operation is idempotent, and the returned result didn’t fetched the records yet.

Returns:

Since:

  • 0.1.0



607
608
609
610
611
612
613
614
615
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 607

def scoped
  scope = @collection

  conditions.each do |(method,*args)|
    scope = scope.public_send(method, *args)
  end

  scope
end

#select(*columns) ⇒ Object

Select only the specified columns.

By default a query selects all the columns of a table (‘SELECT *`).

Examples:

Single column


query.select(:name)

# => SELECT `name` FROM `people`

Multiple columns


query.select(:name, :year)

# => SELECT `name`, `year` FROM `people`

Parameters:

  • columns (Array<Symbol>)

Returns:

  • self

Since:

  • 0.1.0



244
245
246
247
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 244

def select(*columns)
  conditions.push([:select, *columns])
  self
end

#sum(column) ⇒ Numeric

Returns the sum of the values for the given column.

Examples:


query.sum(:comments_count)

# => SELECT SUM(`comments_count`) FROM articles

Parameters:

  • column (Symbol) —

    the column name

Returns:

  • (Numeric)

Since:

  • 0.1.0



441
442
443
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 441

def sum(column)
  run.sum(column)
end

#where(condition = nil, &blk) ⇒ Object Also known as: and

Adds a SQL ‘WHERE` condition.

It accepts a ‘Hash` with only one pair. The key must be the name of the column expressed as a `Symbol`. The value is the one used by the SQL query

Examples:

Fixed value


query.where(language: 'ruby')

# => SELECT * FROM `projects` WHERE (`language` = 'ruby')

Array


query.where(id: [1, 3])

# => SELECT * FROM `articles` WHERE (`id` IN (1, 3))

Range


query.where(year: 1900..1982)

# => SELECT * FROM `people` WHERE ((`year` >= 1900) AND (`year` <= 1982))

Multiple conditions


query.where(language: 'ruby')
     .where(framework: 'lotus')

# => SELECT * FROM `projects` WHERE (`language` = 'ruby') AND (`framework` = 'lotus')

Expressions


query.where{ age > 10 }

# => SELECT * FROM `users` WHERE (`age` > 31)

Parameters:

  • condition (Hash) (defaults to: nil)

Returns:

  • self

Since:

  • 0.1.0



125
126
127
128
 
# File 'lib/lotus/model/adapters/sql/query.rb', line 125

def where(condition = nil, &blk)
  _push_to_conditions(:where, condition || blk)
  self
end