Now that we have our Movie database schema ready we can proceed to create, read, update and delete records.
Loading...
Loading...
Loading...
Loading...
The Cql::Update
class in the CQL (Crystal Query Language) module is designed to represent and execute SQL UPDATE
statements in a clean and structured manner. This guide will walk you through using the class to update records in a database, providing real-world examples and detailed explanations for each method.
Update records in a database with a simple and readable syntax.
Set column values dynamically using hashes or keyword arguments.
Filter records with flexible WHERE
conditions.
Return updated columns after executing the query.
Chainable methods for building complex queries effortlessly.
Let’s start with a simple example of updating a user’s name and age in the users
table.
This example updates the user with id = 1
to have the name "John" and age 30.
Below is a detailed breakdown of the key methods in the Cql::Update
class and how to use them.
table(table : Symbol)
Purpose: Specifies the table to update.
Parameters: table
— A symbol representing the table name.
Returns: Update
object (for chaining).
Real-World Example: Setting the Target Table
This sets the users
table as the target for the update operation.
set(setters : Hash(Symbol, DB::Any))
Purpose: Specifies the column values to update using a hash.
Parameters: setters
— A hash where keys are column names and values are the new values for those columns.
Returns: Update
object (for chaining).
Real-World Example: Updating Multiple Columns
This sets the name
and age
columns to new values for the target record(s).
set(**fields)
Purpose: Specifies the column values to update using keyword arguments.
Parameters: fields
— Column-value pairs as keyword arguments.
Returns: Update
object (for chaining).
Real-World Example: Using Keyword Arguments
This sets the name
to "Alice" and active
to true
.
where(**fields)
Purpose: Adds a WHERE
clause to filter the records to be updated.
Parameters: fields
— A hash where keys are column names and values are the conditions to match.
Returns: Update
object (for chaining).
Real-World Example: Filtering by a Condition
This adds a condition to only update the user where id = 1
.
where(&block)
Purpose: Adds a WHERE
clause using a block for more complex conditions.
Parameters: Block that db_contexts the condition using a filter builder.
Returns: Update
object (for chaining).
Real-World Example: Using a Block for Conditions
This example updates the user where both id = 1
and active = true
.
commit
Purpose: Executes the UPDATE
query and commits the changes to the database.
Returns: A DB::Result
object, which represents the result of the query execution.
Real-World Example: Committing the Update
This commits the changes to the users
table, updating the user with id = 1
.
back(*columns : Symbol)
Purpose: Specifies the columns to return after the update.
Parameters: columns
— An array of symbols representing the columns to return.
Returns: Update
object (for chaining).
Real-World Example: Returning Updated Columns
This will return the updated name
and age
columns after the update.
to_sql(gen = @schema.gen)
Purpose: Generates the SQL query and the parameters required for the UPDATE
statement.
Parameters: gen
— The generator used for SQL generation (default: schema generator).
Returns: A tuple containing the SQL query string and the parameters.
Real-World Example: Generating SQL for an Update
This generates the raw SQL query and its associated parameters without executing it.
Let’s combine multiple methods to handle a more advanced use case. Suppose you want to update a user's data, but only if they are active, and you want to return their updated email address afterward:
In this query:
We specify the users
table.
We update both the name
and email
of the user.
We filter the update to only apply to the active user with id = 1
.
We return the updated email
after the update is committed.
The Cql::Update
class provides a simple yet powerful interface for building and executing UPDATE
queries in a Crystal application. With chainable methods for setting values, applying conditions, and controlling the output, you can easily handle any update operation.
Whether you are updating single records or large batches, the flexibility of Cql::Update
ensures that your queries remain clean, maintainable, and efficient.
The Cql::Insert
class in the CQL (Crystal Query Language) module is a powerful tool designed to simplify the process of inserting records into a database. As a software developer, you'll find this class essential for building INSERT
queries in a clean, readable, and chainable way.
In this guide, we’ll walk through the core functionality, explain each method in detail, and provide real-world examples to help you understand how to integrate Cql::Insert
into your applications.
Insert records into any table with ease.
Insert multiple records in a single query.
Insert records using data fetched from another query.
Get the last inserted ID after an insert.
Chainable, intuitive syntax for building complex queries.
Let’s start with a simple example of inserting a new user into the users
table.
This example demonstrates how you can insert a new user with their name, email, and age using the values
method, followed by commit
to execute the insert.
Let's dive into the individual methods and how you can use them.
into(table : Symbol)
Purpose: Specifies the table into which the data will be inserted. This is your starting point for any insert operation.
Parameter: table
(Symbol) — The name of the table to insert into.
Returns: Insert
object (enabling chaining).
Example:
In the above code, we're targeting the users
table. This is where subsequent data will be inserted.
values(**fields)
Purpose: Specifies the data (fields and values) to insert. The values
method can accept either a hash or keyword arguments.
Parameter: fields
(Hash or keyword arguments) — A mapping of column names to their values.
Returns: Insert
object (for chaining).
Real-World Example 1: Adding a Single User
Here, we’re adding a new user named Bob, specifying their name
, email
, and age
.
Real-World Example 2: Adding Multiple Users in One Query
This example demonstrates how you can insert multiple users in a single query. It’s efficient and reduces database round trips.
last_insert_id(as type : PrimaryKeyType = Int64)
Purpose: Retrieves the ID of the last inserted row. This is incredibly useful when you need to work with the inserted record immediately after an insert, especially in cases where the primary key is automatically generated.
Parameter: type
(default: Int64
) — The data type of the returned ID.
Returns: The last inserted ID as Int64
or the specified type.
Example:
query(query : Query)
Purpose: Instead of manually specifying values, you can use data fetched from another query to populate the insert. This is useful in situations like copying data from one table to another.
Parameter: query
— A query object that fetches the data to insert.
Returns: Insert
object (for chaining).
Real-World Example: Copying Data from One Table to Another
Imagine you want to copy user data from an archive table (archived_users
) to the main users
table.
In this example, we’re selecting all active users from archived_users
and inserting them into the users
table in one go.
back(*columns : Symbol)
Purpose: After an insert operation, you may want to return certain columns, like an ID or timestamp. The back
method allows you to specify which columns should be returned.
Parameter: columns
— One or more symbols representing the columns to return.
Returns: Insert
object (for chaining).
Example:
In this case, after inserting the new user, we’re returning the id
of the newly inserted row.
commit
Purpose: Executes the built INSERT
query and commits the transaction to the database. This is the final step in your insert operation.
Returns: The result of the insert, typically the number of affected rows or the last inserted ID.
Example:
This will execute the INSERT
statement and commit it to the database, saving the new user’s data.
Let’s consider a more advanced example where you insert a new user, return their ID, and use it in subsequent operations.
In this example, after inserting the new user, we immediately get the user_id
and use it to insert data into the user_profiles
table.
In case an insert operation fails, the commit
method automatically logs the error and raises an exception. This allows you to catch and handle the error as needed in your application.
Example:
The Cql::Insert
class provides a flexible, chainable interface for inserting data into a database, whether you're inserting a single record, multiple records, or even records based on the results of a query. Its intuitive API makes it easy to use in real-world applications, and with error handling built-in, it helps ensure robust database operations.
With its simple syntax and powerful features, Cql::Insert
streamlines your database interactions, allowing you to focus on building great features in your Crystal applications.
The Cql::Query
class is designed to simplify the creation and execution of SQL queries. By using a structured, chainable API, you can build complex SQL queries while maintaining clean and readable code.
This guide walks you through how to create, modify, and execute queries using real-world examples. We'll also explore various methods for selecting, filtering, joining, and ordering data, with a focus on practical use cases.
Select columns and filter records using simple, chainable methods.
Join tables for complex queries involving multiple relationships.
Aggregate data using functions like COUNT
, SUM
, and AVG
.
Order and limit your result sets for more precise control.
Fetch results as objects or raw data for immediate use in your application.
Let's begin by selecting user data from a users
table:
This query will return all users with the name "Alice", casting the result to the User
type.
Below is a breakdown of the key methods in the Cql::Query
class and how you can use them in your applications.
select(*columns : Symbol)
Purpose: Specifies the columns to select in the query.
Parameters: columns
— One or more symbols representing the columns you want to select.
Returns: Query
object for chaining.
Real-World Example: Selecting Columns
This query selects the name
and email
columns from the users
table.
from(*tables : Symbol)
Purpose: Specifies the tables to query from.
Parameters: tables
— One or more symbols representing the tables to query from.
Returns: Query
object for chaining.
Real-World Example: Querying a Table
This query selects from the users
table.
where(hash : Hash(Symbol, DB::Any))
Purpose: Adds filtering conditions to the query.
Parameters: hash
— A key-value hash representing the column and its corresponding value for the WHERE
clause.
Returns: Query
object for chaining.
Real-World Example: Filtering Data
This will generate a query with the WHERE
clause: WHERE name = 'Alice' AND age = 30
.
all(as : Type)
Purpose: Executes the query and returns all matching results, casting them to the specified type.
Parameters: as
— The type to cast the results to.
Returns: An array of the specified type.
Real-World Example: Fetching All Results
This will return all users as an array of User
objects.
first(as : Type)
Purpose: Executes the query and returns the first matching result, casting it to the specified type.
Parameters: as
— The type to cast the result to.
Returns: The first matching result of the query.
Real-World Example: Fetching the First Result
This returns the first user with the name "Alice".
count(column : Symbol = :*)
Purpose: Adds a COUNT
aggregate function to the query, counting the specified column.
Parameters: column
— The column to count (default is *
, meaning all rows).
Returns: Query
object for chaining.
Real-World Example: Counting Rows
This will count the number of rows in the users
table and return the result as an Int64
.
join(table : Symbol, on : Hash)
Purpose: Adds a JOIN
clause to the query, specifying the table and the condition for joining.
Parameters:
table
: The table to join.
on
: A hash representing the join condition, mapping columns from one table to another.
Returns: Query
object for chaining.
Real-World Example: Joining Tables
This query joins the users
table with the orders
table on the condition that users.id
equals orders.user_id
.
order(*columns : Symbol)
Purpose: Specifies the columns by which to order the results.
Parameters: columns
— The columns to order by.
Returns: Query
object for chaining.
Real-World Example: Ordering Results
This orders the query results by name
first and then by age
.
limit(value : Int32)
Purpose: Limits the number of rows returned by the query.
Parameters: value
— The number of rows to return.
Returns: Query
object for chaining.
Real-World Example: Limiting Results
This limits the query to return only the first 10 rows.
get(as : Type)
Purpose: Executes the query and returns a scalar value, such as the result of an aggregate function (e.g., COUNT
, SUM
).
Parameters: as
— The type to cast the result to.
Returns: The scalar result of the query.
Real-World Example: Getting a Scalar Value
This returns the total number of users as an Int64
.
each(as : Type, &block)
Purpose: Iterates over each result, yielding each row to the provided block.
Parameters:
as
: The type to cast each row to.
&block
: The block to execute for each row.
Returns: Nothing (used for iteration).
Real-World Example: Iterating Over Results
This will print the name of each user in the users
table.
distinct
Purpose: Sets the DISTINCT
flag to return only unique rows.
Returns: Query
object for chaining.
Real-World Example: Fetching Distinct Results
This will generate a query that returns only distinct rows from the users
table.
Let's create a real-world example that combines several methods. Suppose you want to fetch the first 5 users who have placed an order, ordered by their name, and return the result as User
objects:
In this query:
We select the name
and email
columns from users
.
We join the orders
table to ensure the user has placed an order.
We filter for active users (where(active: true)
).
We order the results by name
.
We limit the results to 5 users.
The Cql::Query
class offers a flexible and intuitive API for building and executing SQL queries in your Crystal applications. With methods for selecting, joining, filtering, and aggregating data, you can handle even the most complex queries with ease.
Whether you're building basic queries or handling complex database operations, the Cql::Query
class provides the tools you need to write clean, efficient, and maintainable code.
The Cql::Delete
class provides a structured and flexible way to build and execute SQL DELETE
queries in your Crystal applications. This guide will help you understand how to create delete queries, apply conditions, and execute them to remove records from your database.
Delete records from any table in a straightforward manner.
Filter records to delete using flexible WHERE
conditions.
Return columns after deletion if needed.
Chainable syntax for clean and maintainable query building.
Let’s start with a simple example of deleting a user from the users
table where the id
is 1.
This query deletes the record in the users
table where id = 1
.
The following section provides a breakdown of the key methods available in the Cql::Delete
class and how to use them effectively.
from(table : Symbol)
Purpose: Specifies the table from which records will be deleted.
Parameters: table
— A symbol representing the table name.
Returns: Delete
object (for chaining).
Real-World Example: Specifying the Table
This sets the users
table as the target for the delete operation.
where(**fields)
Purpose: Adds a WHERE
clause to filter the records to be deleted.
Parameters: fields
— A key-value hash where keys represent column names and values represent the conditions to match.
Returns: Delete
object (for chaining).
Real-World Example: Filtering by Conditions
This filters the query to only delete the user where id = 1
.
where(&block)
Purpose: Adds a WHERE
clause using a block for more complex filtering conditions.
Parameters: A block that defines the filtering logic using a filter builder.
Returns: Delete
object (for chaining).
Real-World Example: Using a Block for Conditions
This deletes all users where the age
is less than 30.
commit
Purpose: Executes the delete query and commits the changes to the database.
Returns: A DB::Result
object representing the result of the query execution.
Real-World Example: Committing the Delete
This deletes the user from the users
table where id = 1
and commits the change.
using(table : Symbol)
Purpose: Adds a USING
clause to the delete query, useful when deleting records based on conditions from another table.
Parameters: table
— A symbol representing the name of the table to use in the USING
clause.
Returns: Delete
object (for chaining).
Real-World Example: Using Another Table for Deletion
This example deletes users where they are linked to posts based on the condition posts.user_id = users.id
.
back(*columns : Symbol)
Purpose: Specifies the columns to return after the delete operation.
Parameters: columns
— An array of symbols representing the columns to return.
Returns: Delete
object (for chaining).
Real-World Example: Returning Columns After Deletion
This deletes the user with id = 1
and returns the name
and email
of the deleted record.
to_sql(gen = @schema.gen)
Purpose: Generates the SQL query and parameters required for the delete operation.
Parameters: gen
— The generator used for SQL generation (default: schema generator).
Returns: A tuple containing the SQL query string and the parameters.
Real-World Example: Generating SQL for Deletion
This generates the raw SQL query and its associated parameters without executing it.
Let’s combine multiple methods to handle a more advanced use case. Suppose you want to delete a user from the users
table where they have no associated posts, and you want to return the deleted user’s name and email:
In this query:
We specify the users
table as the target for deletion.
We use the posts
table to filter users without any posts.
We return the name
and email
of the deleted user(s).
The Cql::Delete
class provides an intuitive and powerful interface for deleting records in your Crystal applications. With chainable methods for setting conditions, joining tables, and selecting return columns, you can easily construct and execute delete queries with precision and clarity.
Whether you need to delete specific records or perform complex, condition-based deletions, the Cql::Delete
class ensures that your queries are efficient and maintainable.