Query

Read (query) operations are provided by cql via its compiled query system.

Query creation

To create a query you must use the cql.Query[models.MyModel] method, where models.MyModel is the model you expect this query to answer. This function takes as parameters the transaction on which to execute the query and the Conditions.

Transactions

To execute transactions, cql provides the function cql.Transaction. The function passed by parameter will be executed inside a gorm transaction (for more information visit https://gorm.io/docs/transactions.html). Using this method will also allow the transaction execution time to be logged.

Query methods

The object obtained using cql.Query has different methods that will allow you to obtain the results of the query:

Modifier methods

Modifier methods are those that modify the query in a certain way, affecting the results obtained: - Limit: specifies the number of models to be retrieved. - Offset: specifies the number of models to skip before starting to return the results. - Ascending: specifies an ascending order when retrieving models. - Descending: specifies a descending order when retrieving models from database.

Finishing methods

Finishing methods are those that cause the query to be executed and the result(s) of the query to be returned:

Count: returns the amount of models that fulfill the conditions.
First: finds the first model ordered by primary key.
Take: finds the first model returned by the database in no specified order.
Last: finds the last model ordered by primary key.
FindOne: finds the only one model that matches given conditions or returns error if 0 or more than 1 are found.
Find: finds list of models that meet the conditions.

Conditions

The set of conditions that are received by the cql.Query method form the cql compiled query system. It is so named because the conditions will verify at compile time that the query to be executed is correct.

These conditions are objects of type Condition that contain the necessary information to perform the queries in a safe way. They are generated from the definition of your models using cql-gen.

Examples

Filter by an attribute

In this example we query all MyModel that has “a_string” in the Name attribute.

type MyModel struct {
    model.UUIDModel

    Name string
}

myModels, err := cql.Query[MyModel](
    gormDB,
    conditions.MyModel.Name.Is().Eq("a_string"),
).Find()

Filter by an attribute of a related model

In this example we query all MyModels whose related MyOtherModel has “a_string” in its Name attribute.

type MyOtherModel struct {
    model.UUIDModel

    Name string
}

type MyModel struct {
    model.UUIDModel

    Related   MyOtherModel
    RelatedID model.UUID
}

myModels, err := cql.Query[MyModel](
    gormDB,
    conditions.MyModel.Related(
        conditions.MyOtherModel.Name.Is().Eq("a_string"),
    ),
).Find()

Multiple conditions

In this example we query all MyModels that has a 4 in the Code attribute and whose related MyOtherModel has “a_string” in its Name attribute.

type MyOtherModel struct {
    model.UUIDModel

    Name string
}

type MyModel struct {
    model.UUIDModel

    Code int

    Related   MyOtherModel
    RelatedID model.UUID
}

myModels, err := cql.Query[MyModel](
    gormDB,
    conditions.MyModel.Code.Is().Eq(4),
    conditions.MyModel.Related(
        conditions.MyOtherModel.Name.Is().Eq("a_string"),
    ),
).Find()

Operators

The different operators to use inside your queries are defined by the methods of the FieldIs type, which is returned when calling the Is() method. Below you will find the complete list of available operators:

Eq(value): Equal to
NotEq(value): Not equal to
Lt(value): Less than
LtOrEq(value): Less than or equal to
Gt(value): Greater than
GtOrEq(value): Greater than or equal to
Null()
NotNull()
Between(v1, v2): Equivalent to v1 < attribute < v2
NotBetween(v1, v2): Equivalent to NOT (v1 < attribute < v2)
Distinct(value)
NotDistinct(value)
In(values)
NotIn(values)

For boolean attributes:

True()
NotTrue()
False()
NotFalse()
Unknown(): unknown is null for booleans
NotUnknown(): unknown is null for booleans

For string attributes:

Like(pattern)

In addition to these, cql gives the possibility to use operators that are only supported by a certain database (outside the standard). For doing it, you must use the Custom method and give the operator as argument, for example:

conditions.MyModel.Code.Is().Custom(psql.ILike("_a%")),

These operators can be found in <https://pkg.go.dev/github.com/FrancoLiberali/cql/mysql>, <https://pkg.go.dev/github.com/FrancoLiberali/cql/sqlserver>, <https://pkg.go.dev/github.com/FrancoLiberali/cql/psql> and <https://pkg.go.dev/github.com/FrancoLiberali/cql/sqlite>.

You can also define your own operators following the condition.Operator interface.