class CQL::Delete

Overview

A delete query This class represents a delete query It provides methods for building a delete query It also provides methods for executing the query

All delete operations are automatically tracked by CQL::Performance when enabled.

Example Deleting a record

AcmeDB.delete.from(:users).where(id: 1).commit

Included Modules

Defined in:

delete.cr

Constructors

Instance Method Summary

Instance methods inherited from module CQL::Pinnable

executor : Executor executor, schema : Schema schema, using(connection : DB::Connection) : self
using(executor : Executor) : self using

Constructor Detail

def self.new(schema : Schema) #

Initialize the delete query

  • @param schema [Schema] The schema to use
  • @return [Delete] The delete query object

Example Deleting a record

CQL::Delete.new(AcmeDB) # same as AcmeDB.delete
  .from(:users)
  .where(id: 1)
  .commit

[View source]

Instance Method Detail

def back(*columns : Symbol) #

Sets the columns to return after the delete (RETURNING; not on SQLite)

  • @param columns [Symbol*] The columns to return
  • @return [self] The current instance
  • @raise [Exception] If the column does not exist

Example Setting the columns to return

AcmeDB.delete.from(:users).where(id: 1).back(:name, :age)

[View source]
def build #

Builds the delete expression

  • @return [Expression::Delete] The delete expression
  • @raise [Exception] If the table is not set

Example Building the delete expression

AcmeDB.delete.from(:users).where(id: 1).build

[View source]
def commit #

Executes the delete query and returns the result The query execution is automatically tracked by CQL::Performance when enabled.

  • @return [DB::ExecResult] The result of the query

Example Deleting a record

result = AcmeDB.delete.from(:users).where(id: 1).commit
result.rows_affected

[View source]
def from(table : Symbol) #

Sets the table to delete from

  • @param table [Symbol] The name of the table
  • @return [self] The current instance
  • @raise [ArgumentError] If the table does not exist

Example Setting the table

AcmeDB.delete.from(:users)

[View source]
def schema : Schema #

[View source]
def to_sql(gen : Expression::Generator | Nil = nil) #

Generates the SQL query and parameters

  • @param gen [Expression::Generator] The generator to use
  • @return [{String, Array(DB::Any)}] The query and parameters

Example Generating a delete query

AcmeDB.delete.from(:users).where(id: 1).to_sql
# => {"DELETE FROM users WHERE users.id = ?", [1]}

[View source]
def using(table : Symbol) #

Sets the table to use in the using clause

  • @param table [Symbol] The name of the table
  • @return [self] The current instance
  • @raise [ArgumentError] If the table does not exist

Example Setting the using table

AcmeDB.delete.from(:posts).using(:users)
  .where { (posts.user_id == users.id) & (users.active == false) }
  .to_sql(Expression::Generator.new(CQL::Adapter::Postgres))
# => {"DELETE FROM posts USING users WHERE (posts.user_id = users.id) AND (users.active = $1)", [false]}

MySQL renders DELETE FROM posts USING posts, users ...; SQLite has no such form, so generating the SQL raises CQL::DialectError there.


[View source]
def where(&) #

Sets the WHERE clause using a block evaluated on an Expression::FilterBuilder, where the FROM and USING tables are reachable by name

  • @return [self] The current instance

Example Setting the where clause

AcmeDB.delete.from(:users).where { users.name == "John" }

[View source]
def where(attr : Hash(Symbol, DB::Any)) #

Where clause using a hash of conditions to match against

  • @param attr [Hash(Symbol, DB::Any)] The conditions to match against
  • @return [self] The current instance

Example Setting the where clause

conditions = {:id => 1} of Symbol => DB::Any
AcmeDB.delete.from(:users).where(conditions)

[View source]
def where(__sql sql : String, *positional, **named) #

A raw SQL fragment with bound ? / :name values (sql must be a literal).


[View source]
def where(**fields) #

Where clause using keyword conditions, ANDed together

  • @param fields [NamedTuple] The conditions to match against
  • @return [self] The current instance

Example Setting the where clause

AcmeDB.delete.from(:users).where(name: "John", active: false)

[View source]