Quickstart

When working with DSQL you need to understand the following basic concepts:

Basic Concepts

Expression (see expr)
Expression object, represents a part of a SQL query. It can be used to express advanced logic in some part of a query, which Query itself might not support or can express a full statement Never try to look for “raw” queries, instead build expressions and think about escaping.
Query (see query)
Object of a Query class can be used for building and executing valid SQL statements such as SELECT, INSERT, UPDATE, etc. After creating Query object you can call various methods to add “table”, “where”, “from” parts of your query.
Connection
Represents a connection to the database. If you already have a PDO object you can feed it into Expression or Query, but for your comfort there is a Connection class with very little overhead.

Getting Started

We will start by looking at the Query building, because you do not need a database to create a query:

use atk4\dsql\Query;

$query = new Query(['connection' => $pdo]);

Once you have a query object, you can add parameters by calling some of it’s methods:

$query
    ->table('employees')
    ->where('birth_date', '1961-05-02')
    ->field('count(*)')
    ;

Finally you can get the data:

$count = $query->getOne();

While DSQL is simple to use for basic queries, it also gives a huge power and consistency when you are building complex queries. Unlike other query builders that sometimes rely on “hacks” (such as method whereOr()) and claim to be useful for “most” database operations, with DSQL, you can use DSQL to build ALL of your database queries.

This is hugely beneficial for frameworks and large applications, where various classes need to interact and inject more clauses/fields/joins into your SQL query.

DSQL does not resolve conflicts between similarly named tables, but it gives you all the options to use aliases.

The next example might be a bit too complex for you, but still read through and try to understand what each section does to your base query:

// Establish a query looking for a maximum salary
$salary = new Query(['connection'=>$pdo]);

// Create few expression objects
$e_ms = $salary->expr('max(salary)');
$e_df = $salary->expr('TimeStampDiff(month, from_date, to_date)');

// Configure our basic query
$salary
    ->table('salary')
    ->field(['emp_no', 'max_salary'=>$e_ms, 'months'=>$e_df])
    ->group('emp_no')
    ->order('-max_salary')

// Define sub-query for employee "id" with certain birth-date
$employees = $salary->dsql()
    ->table('employees')
    ->where('birth_date', '1961-05-02')
    ->field('emp_no')
    ;

// Use sub-select to condition salaries
$salary->where('emp_no', $employees);

// Join with another table for more data
$salary
    ->join('employees.emp_id', 'emp_id')
    ->field('employees.first_name');


// Finally, fetch result
foreach ($salary as $row) {
    echo "Data: ".json_encode($row)."\n";
}

The above query resulting code will look like this:

SELECT
    `emp_no`,
    max(salary) `max_salary`,
    TimeStampDiff(month, from_date, to_date) `months`
FROM
    `salary`
JOIN
    `employees` on `employees`.`emp_id` = `salary`.`emp_id`
WHERE
    `salary`.`emp_no` in (select `id` from `employees` where `birth_date` = :a)
GROUP BY `emp_no`
ORDER BY max_salary desc

:a = "1961-05-02"

Using DSQL in higher level ORM libraries and frameworks allows them to focus on defining the database logic, while DSQL can perform the heavy-lifting of query building and execution.

Creating Objects and PDO

DSQL classes does not need database connection for most of it’s work. Once you create new instance of Expression or Query you can perform operation and finally call Expression::render() to get the final query string:

use atk4\dsql\Query;

$q = (new Query())->table('user')->where('id', 1)->field('name');
$query = $q->render();
$params = $q->params;

When used in application you would typically generate queries with the purpose of executing them, which makes it very useful to create a Connection object. The usage changes slightly:

$c = atk4\dsql\Connection::connect($dsn, $user, $password);
$q = $c->dsql()->table('user')->where('id', 1)->field('name');

$name = $q->getOne();

You no longer need “use” statement and Connection class will automatically do some of the hard work to adopt query building for your database vendor. There are more ways to create connection, see `Advanced Connections`_ section.

The format of the $dsn is the same as with PDO class. If you need to execute query that is not supported by DSQL, you should always use expressions:

$tables = $c -> expr('show tables like []', [$like_str])->get();

DSQL classes are mindful about your SQL vendor and it’s quirks, so when you’re building sub-queries with Query::dsql, you can avoid some nasty problems:

$sqlite_c ->dsql()->table('user')->truncate();

The above code will work even though SQLite does not support truncate. That’s because DSQL takes care of this.

Query Building

Each Query object represents a query to the database in-the-making. Calling methods such as Query::table or Query::where affect part of the query you’re making. At any time you can either execute your query or use it inside another query.

Query supports majority of SQL syntax out of the box. Some unusual statements can be easily added by customizing template for specific query and we will look into examples in Extending Query Class

Query Mode

When you create a new Query object, it is going to be a SELECT query by default. If you wish to execute update operation instead, you simply call Query::update, for delete - Query::delete (etc). For more information see Query Modes. You can actually perform multiple operations:

$q = $c->dsql()->table('employee')->where('emp_no', 1234);
$backup_data = $q->get();
$q->delete();

A good practice is to re-use the same query object before you branch out and perform the action:

$q = $c->dsql()->table('employee')->where('emp_no', 1234);

if ($confirmed) {
    $q->delete();
} else {
    echo "Are you sure you want to delete ".$q->field('count(*)')." employees?";
}

Fetching Result

When you are selecting data from your database, DSQL will prepare and execute statement for you. Depending on the connection, there may be some magic involved, but once the query is executed, you can start streaming your data:

foreach ($query->table('employee')->where('dep_no',123) as $employee) {
    echo $employee['first_name']."\n";
}

In most cases, when iterating you’ll have PDOStatement, however this may not always be the case, so be cautious. Remember that DQSL can support vendors that PDO does not support as well or can use Proxy Connection. In that case you may end up with other Generator/Iterator but regardless, $employee will always contain associative array representing one row of data. (See also `Manual Query Execution`_).