Query builder

The Query Builder simplifies running queries against the database, as it does not require you to write the queries by hand, and thus introduce a different language into your PHP source code. The Query Builder does however, not just build the queries for you, it also executes them.

The Query Builder is available in the Base Model as well as in the Database Library. Both use same methods, but the Database Library methods have an extra input argument, the table name must be added as the first argument in every method call. For this reason it is recommended that you use the Base Model and this documentation will cover only this scenario as well.

Creating data

Data creation is simply handled by the create method. It takes an associative array as input, where the keys represent the columns, and the values represent the data to be inserted:

<?php
// code ...
$model->create([
    "col1"  =>  "value",
    "col2"  =>  1
]);
// code ...

The above example will generate and execute the following query:

INSERT INTO "table" ("col1", "col2") VALUES ('value', 1)

The create method returns a bool status.

Updating existing data

Updating data is also simply done with the update method. It requires an associative array of column names as keys and new values as array values as the input argument. Upon a successful update, boolean value true is returned, or false on error.

The update method of the query builder will also create the WHERE statements for the data retrieval query. Refer to Where statements part of this documentation for more information on building WHERE statements. The bellow example shows a simple update query:

<?php
// code ...
$model->update(["col1" => "foo"]);
// code ...

The above example will generate and execute the following query:

UPDATE "table" SET "table"."col1" = 'foo' WHERE 1=1

Deleting data

Data deletion is as simple as data creation, the delete method will generate a DELETE statement and execute it, and return a bool status. To limit deletion the Query Builder will generate WHERE statements for the query as well. Refer to Where statements part of this documentation for more information on building WHERE statements. Bellow example will delete all records from a database table:

<?php
// code ...
$model->delete();
// code ...

The above example will generate and execute the following query:

DELETE FROM "table" WHERE 1=1

Soft deletes

Attention

Soft deletes are possible only with the Base Model, the Database Library does not support it!

Soft deletion does not delete the data row from the database, but only marks them as deleted with a special column. By default this feature is disabled, and calling the delete the data row will be deleted from the database permanently.

To use soft deletes they have to be enabled in the database.php configuration file, by setting the softDeletes to true. By default, the soft deleted rows are marked by setting the boolean value of true to the deleted column.

The softDeleteTimestamp configuration option is set to false by default, if you set it to true, then instead of setting a boolean value of true to the deleted column the soft delete will write a timestamp into the deleted column. The column must be nullable, as not deleted items will have the value of NULL set.

If you wish to keep the records of deletion in a different column, you can set it in the softDeleteCol configuration option.

Data retrieval

To obtain data the query builder provides the select method which will generate the SELECT SQL statement, execute it and return the Result object. More information about obtaining data from the Result object can be found in the Fetching data section of the documentation.

You are required to supply an array of the column names that you want in the SELECT statement as the first and only parameter of the select method call.

The select method of the query builder will also create the WHERE statements for the data retrieval query. Refer to Where statements part of this documentation for more information on building WHERE statements. The bellow example shows a simple data retrieval query:

<?php
// code ...
$result = $model->select(["col1"]);
// code ...

The above example will generate and execute the following query:

SELECT "col1" FROM "table" WHERE 1=1

A successful execution will return the Result object, as well as store it inside the Base Model. You can obtain data from the returned Result object or directly from the model itself, using the exact same method. Fetching data is already covered in the linked documentation, please refer to it for more information.

If you attempt to access results through the model and a successful data retrieval was not made before, the model will throw a \SlaxWeb\Database\Exception\NoDataException.

Where statements

WHERE statements help you limit the range of rows in the database on which you execute the query. Be it data obtaining, data deletion, or data amending, the query builder will automatically try and create a WHERE statement for the next query, if you have set it up. To do so, the query builder provides the following methods:

• where
• orWhere
• groupWhere
• orGroupWhere
• nestedWhere
• orNestedWhere

As you may have already noticed, the Query Builder adds the first static predicate, 1=1 to all queries, no matter if custom WHERE predicates follow or not. This is done to simplify and enhance performance of the query builder.

For examples bellow, only the select method is used, but WHERE statements can be combined with other queries as well.

where

The where method creates a simple WHERE predicate. It will link it to other predicates with the logical AND operator. It takes the name of the column as the first argument, and the value as the second argument. The last argument defines the logical operator for the column, default being the equal symbol (=). The example shows how you use the where method, and how you can alter the logical operator:

<?php
// code ...
$model->where("col1", "foo")
    ->where("col2", ["bar", "baz"], "IN")
    ->select("col3");
// code ...

The above example will generate and execute the following query:

SELECT
    "table"."col2"
FROM
    "table"
WHERE
    1=1
    AND "table"."col1" = 'foo'
    AND "table"."col2" IN ('bar','baz)';

orWhere

orWhere is exactly the same as where only it will link the predicate with a logical OR operator to the previous predicate:

Warning

Using OR versions is not recommended as the first predicate, as it will contain all rows because of the initial 1=1 predicate in every query.

<?php
// code ...
$model->where("col1", "foo")
    ->orWhere("col2", "bar%", "LIKE")
    ->select("col3");
// code ...

The above example will generate and execute the following query:

SELECT
    "table"."col2"
FROM
    "table"
WHERE
    1=1
    AND "table"."col1" = 'foo'
    OR "table"."col2" LIKE 'bar%';

groupWhere

Grouping WHERE predicates is nearly a necessity in more complex statements. To be able to group predicates the Query Builder provides the groupWhere method. The method takes a Closure as its only parameter. The Closure will receive an instance of a Query Builder as input, you can use all the where methods as normal:

<?php
// code ...
$model->groupWhere(function ($builder) {
        $builder->where("col1", "foo")
            ->orWhere("col2", "bar");
    })->groupWhere(function ($builder) {
        $builder->where("col3", "baz")
            ->orWhere("col4", "qux");
    })->select("col5");
// code ...

The above example will generate and execute the following query:

SELECT
    "table"."col5"
FROM
    "table"
WHERE
    1=1
    AND (
        "table"."col1" = 'foo'
        OR "table"."col2" = 'bar'
    ) AND (
        "table"."col3" = 'baz'
        OR "table"."col4" = 'qux'
    );

orGroupWhere

orGroupWhere is exactly the same as groupWhere only it will link the grouped predicates with a logical OR operator to the previous predicate:

Warning

Using OR versions is not recommended as the first predicate, as it will contain all rows because of the initial 1=1 predicate in every query.

<?php
// code ...
$model->groupWhere(function ($builder) {
        $builder->where("col1", "foo")
            ->orWhere("col2", "bar");
    })->orGroupWhere(function ($builder) {
        $builder->where("col3", "baz")
            ->orWhere("col4", "qux");
    })->select("col5");
// code ...

The above example will generate and execute the following query:

SELECT
    "table"."col5"
FROM
    "table"
WHERE
    1=1
    AND (
        "table"."col1" = 'foo'
        OR "table"."col2" = 'bar'
    ) OR (
        "table"."col3" = 'baz'
        OR "table"."col4" = 'qux'
    );

nestedWhere

The nestedWhere method allows to bring in a SELECT statement as a WHERE predicate. It functions similarly as groupWhere, only it takes the name of the column as the first parameter, and the Closure as the second. The Closure again receives the Query Builder as input. You must set the table name to that instance of the Query Builder, and then use it as you have used it before:

<?php
// code ...
$model->nestedWhere("col1", function ($builder) {
    return $builder->table("table2")
        ->select(["col1"]);
})->select("col2");
// code ...

The above example will generate and execute the following query:

SELECT
    "col2"
FROM
    "table1"
WHERE
    1=1
    AND "table1"."col1" IN (
        SELECT "table2"."col1" FROM "table2" WHERE 1=1
    );

orNestedWhere

orNestedWhere is exactly the same as nestedWhere only it will link the grouped predicates with a logical OR operator to the previous predicate:

Warning

Using OR versions is not recommended as the first predicate, as it will contain all rows because of the initial 1=1 predicate in every query.

<?php
// code ...
$model->where("col3", "foo")
    ->orNestedWhere("col1", function ($builder) {
    return $builder->table("table2")
        ->select(["col1"]);
})->select("col2");
// code ...

The above example will generate and execute the following query:

SELECT
    "col2"
FROM
    "table1"
WHERE
    1=1
    AND "table1"."col3" = 'foo'
    OR "table1"."col1" IN (
        SELECT "table2"."col1" FROM "table2" WHERE 1=1
    );

Joins

Joining on other or same tables is done simply with the Query Builder. It supports multiple joins, multiple join conditions, and multiple join types. For this the Query Builder supplies the following methods:

• join
• joinModel
• joinCond
• orJoinCond
• joinCols

And the following constants in the \SlaxWebDatabaseBaseModel class for join types:

• JOIN_INNER - INNER JOIN
• JOIN_LEFT - LEFT OUTER JOIN
• JOIN_RIGHT - RIGHT OUTER JOIN
• JOIN_FULL - FULL OUTER JOIN
• JOIN_CROSS - CROSS JOIN

join

The join method defines the table to which the SQL JOIN is to be made, and which type of join. The method takes the name of the table as an operator, and the type, which defaults to the INNER JOIN type.

The example bellow shows basic usage of the join method:

<?php
// code ...
$model->join("table2", BaseModel::JOIN_LEFT);
// code ...

If only the join method is called, an Exception will be thrown when executing the query, the joinCond has to be called at least once and define a valid join condition.

joinModel

The joinModel method conveniently extracts the table name from the joining model and the primary key, allowing you to skip the joinCond call. The method takes the Model object as first parameter, second parameter is the foreign key of the main table, and the third parameter defines the type of join, which defaults to the INNER JOIN type. The final parameter defines the comparison operator which defaults to equals (=).

The joinModel requires the joining model to have the $primaryKey protected property properly set, otherwise a \SlaxWeb\DatabasePDO\Exception\NoPrimKeyException is thrown. Example usage:

<?php
// code ...
$model->join($otherModel, "foreign_key_column", BaseModel::JOIN_LEFT, "=");
// code ...

The above code will generate the following JOIN statement:

LEFT OUTTER JOIN "otherModelTable"
    ON ("table1"."foreign_key_column" = "otherModelTable"."primKey")

joinCond

The joinCond method defines the join condition(s) for the last added join with the join or joinModel method calls. If the joinCond is called before either of those methods is called, an \SlaxWeb\DatabasePDO\Exception\NoJoinTableException will be thrown. The method takes the main table column name on which the join is to be made, and the column of the joining table. The third parameter defines the logical operator for the join condition which defaults to is equal (=).

The bellow example is a continuation from the above examples:

<?php
// code ...
$model->joinCond("col1", "col2");
// code ...

orJoinCond

orJoinCond is exactly the same as joinCond only it will link the join condition with a logical OR operator to the previous join condition.

The bellow example is a continuation from the above examples:

<?php
// code ...
$model->orJoinCond("col3", "col4");
// code ...

joinCols

The joinCols method is the final method for the JOIN statement. It is not required, and the Query Builder will build a JOIN statement without it just fine. The joinCols method defines which columns are to be put on the select column list from the joining table.

The bellow example is a continuation from the above examples:

<?php
// code ...
$model->joinCols(["col5"])->select(["col1"]);
// code ...

The above examples will generate and execute the following query:

SELECT
    "table1"."col1",
    "table2"."col5"
FROM
    "table1"
LEFT OUTTER JOIN "table2"
    ON ("table1"."col1" = "table2"."col2")
    OR ("table1"."col3" = "table2"."col4")
WHERE
    1=1

Grouping

For column grouping the Query Builder provides a simple groupBy method that adds the column to the group by list. It accepts the name of the column as an input parameter:

<?php
// code ...
$model->groupBy("col1")
    ->select(["col1"]);
// code ...

The above examples will generate and execute the following query:

SELECT
    "table"."col1"
FROM
    "table"
WHERE
    1=1
GROUP BY
    "table"."col1"

Ordering

To create an ORDER BY statement, the Query Builder provides a orderBy method. The orderBy method takes the name of the column as the first input argument, the second input argument defines the direction of the order, and it defaults to ASC. The third argument may define a function to be used for that order, by default it is an empty string:

<?php
// code ...
$model->orderBy("col1", \\SlaxWeb\Database\BaseModel::ORDER_DESC, "sum")
    ->select([["func" => "sum", "col" => "col1", "as" => "summary"]]);
// code ...

The above examples will generate and execute the following query:

SELECT
    SUM("table"."col1") AS summary
FROM
    "table"
WHERE
    1=1
ORDER BY
    SUM("table"."col1") DESC

Limit

The Query Builder also allows you to limit the results with SQL LIMIT and OFFSET. To do so, the method limit is provided, and it accepts two integers as input, first one being LIMIT and second one being OFFSET. Offset is by default 0, and usage is simple as with other methods, you just need to ensure it gets called before calling the select, update, or delete methods:

<?php
// code ...
$model->limit(5, 10)
    ->select(["col1"]);
// code ...

The above examples will generate and execute the following query:

SELECT
    "table"."col1"
FROM
    "table"
WHERE
    1=1
LIMIT
    5
OFFSET
    10

Timestamps

Attention

Timestamps are possible only with the Base Model, the Database Library does not support it!

The Timestamp feature has to be enabled in the database.php configuration file to have the component automatically stamp the rows when they are created and updated. The column names and values can be configured through the configuration. The configuration is valid for all models, but all values can also be overriden in each model. Bellow you can find a list of options for the Timestamps that can be edited in the timestamp configuration option in the database.php configuration file.

enabled

• Data type: bool
• Default: false

The enabled option turns Timestamping on and off.

To enable or disabled timestamping only in a specific model, set its $timestamps protected property to either bool(true) or bool(false)

createdColumn

• Data type: string
• Default: created_at

The name of the column for the created timestamp. When a row is created, the database component will write the timestamp to this column.

To alter this in a specific model, you can override this setting by definint a string value to the $createdColumn protected property.

Note

You can set both the createdColumn and updatedColumn to the same value to keep timestamps in only one column in the database.

updatedColumn

• Data type: string
• Default: updated_at

The name of the column for the updated timestamp. When a row is updated, the database component will write the timestamp to this column.

To alter this in a specific model, you can override this setting by definint a string value to the $updatedColumn protected property.

Note

You can set both the createdColumn and updatedColumn to the same value to keep timestamps in only one column in the database.

function

• Data type: string
• Default: NOW()

The SQL function to be used as the value of the timestamp. No other method of timestamping is supported.

If you wish to alter the function for a specific model you can do so by setting a string value to the timestampFunction protected property.

SQL functions

The Query Builder also provides possibility to add built in SQL functions to the column list in select, insert, update, and join statements, as MAX, COUNT, and so on. To do so, you have to provide a nested array in the select, insert, update, or joinCols column lists. The nested array has to be associative, it needs to hold the func key, which defines the SQL function, the column name as the col key, and an optional as key that will define the name alias for that SQL function:

<?php
// code ...
$model->select([["func" => "count", "col" => "col1", "as" => "rowcount"]);
// code ...

The above examples will generate and execute the following query:

SELECT
    COUNT("table"."col1") AS rowcount
FROM
    "table"
WHERE
    1=1

Note

As of version 0.5 SQL functions are also supported in where statements.