Managing SQL queries

One of the oldest part of the library is the Query Builder, a set of classes used to create SQL queries for OrientDB.

If you are familiar with Doctrine, the synthax will be straightforward:

$query->where('cats = ?', 'dogs')->limit(12);

Installing the query builder

Since it comes bundled with Orient, you only need to import the namespace:

use Congow\Orient\Query;

$query = new Query();
$query->select...

Executing queries

Executing queries is pretty easy: you only need to extract, from a Query object, the SQL after you set a few parameters ( WHERE conditions, LIMITs and so on ).

To extract the SQL from a Query object, just use the getRaw() method:

$query = new Query;
$query->from(array('users'))->where('username = ?', "admin");

echo $query->getRaw();
// SELECT FROM USERS WHERE username = "admin"

Here’s a non exhaustive list of methods available within the Query object:

* where($condition, $value)
* andWhere($condition, $value)
* orWhere($condition, $value)
* from(Array $from)
* resetWhere()

Other methods are available based on the type of Query you are doing.

For example, when executing a SELECT, you are able to call:

* limit($int)
* range($rid1, $rid2)
* between($key, $left, $right)
* orderBy($what)

and so on.

For a greater overview of available methods, take a look at the sources of the Congow\Orient\Query\Command class and all the classes extending it.

Internals

When creating a query, you will call a few methods that set the internal command used, for example:

$query->update(...)

will tell the Query object to use an internal command of type Congow\Orient\Query\Command\Update to perform the SQL manipulation.

So, as you surely understand, the Query class is a wrapper for commands.

Each command resides under Congow\Orient\Query\Command and is a class having a SCHEMA, which defines the parts of the SQL.

For example, a custom command might be:

class MyCommand extends Command
{
  const SCHEMA = "EXECUTE PROCEDURE :Procedure"

  public function __construct($procedure)
  {
    $this->setToken('Procedure', $procedure);
  }
}

If you integrate it in the Query class, you would probably add a method called ->myCommand($procedure):

public function myCommand($procedure)
{
  $class = $this->getCommandClass('MyCommand'); // returns an Congow\Orient\Query\Command\MyCommans, as string
  $this->command = new $class($rocedure);

  return $this->command;
}

So, basically, you force the Query act as a proxy.

When defining a SCHEMA you’ll notice that some string are preceeded by :: this means they are tokens, thus string replaceable with the ->setToken() and ->setTokenValues() methods.

A typical example of token is the :From:

SELECT * FROM :From

When you call ->setToken(‘PHP’) the string gets tranformed:

SELECT * FROM PHP

Transformations are done accordingly to the Formatters associated to a token, defined in the ‘->setTokenFormatters()’ method of each command.

For example, the SELECT command has this SCHEMA and formatters:

SELECT :Projections FROM :Target :Where :Between :OrderBy :Limit :Range

'Projections' => "Congow\Orient\Formatter\Query\Regular",
'OrderBy'     => "Congow\Orient\Formatter\Query\OrderBy",
'Limit'       => "Congow\Orient\Formatter\Query\Limit",
'Range'       => "Congow\Orient\Formatter\Query\Range",
'Between'     => "Congow\Orient\Formatter\Query\Between",
...

Each formatter is a class implementing the Congow\Orient\Contract\Formatter\Query\Token interface.

Extending the Query Builder

When instantiating a Query object, you are able to inject custom classes used to handle commands.

Default ones are:

'select'            => 'Congow\Orient\Query\Command\Select',
'insert'            => 'Congow\Orient\Query\Command\Insert',
'delete'            => 'Congow\Orient\Query\Command\Delete',
'update'            => 'Congow\Orient\Query\Command\Update',
'update.add'        => 'Congow\Orient\Query\Command\Update\Add',
'update.remove'     => 'Congow\Orient\Query\Command\Update\Remove',
'update.put'        => 'Congow\Orient\Query\Command\Update\Put',
'grant'             => 'Congow\Orient\Query\Command\Credential\Grant',
'revoke'            => 'Congow\Orient\Query\Command\Credential\Revoke',
'class.create'      => 'Congow\Orient\Query\Command\OClass\Create',
'class.drop'        => 'Congow\Orient\Query\Command\OClass\Drop',
'class.alter'       => 'Congow\Orient\Query\Command\OClass\Alter',
'truncate.class'    => 'Congow\Orient\Query\Command\Truncate\OClass',
'truncate.cluster'  => 'Congow\Orient\Query\Command\Truncate\Cluster',
'truncate.record'   => 'Congow\Orient\Query\Command\Truncate\Record',
'references.find'   => 'Congow\Orient\Query\Command\Reference\Find',
'property.create'   => 'Congow\Orient\Query\Command\Property\Create',
'property.drop'     => 'Congow\Orient\Query\Command\Property\Drop',
'property.alter'    => 'Congow\Orient\Query\Command\Property\Alter',
'index.drop'        => 'Congow\Orient\Query\Command\Index\Drop',
'index.create'      => 'Congow\Orient\Query\Command\Index\Create',
'index.count'       => 'Congow\Orient\Query\Command\Index\Count',
'index.put'         => 'Congow\Orient\Query\Command\Index\Put',
'index.remove'      => 'Congow\Orient\Query\Command\Index\Remove',
'index.lookup'      => 'Congow\Orient\Query\Command\Index\Lookup',
'link'              => 'Congow\Orient\Query\Command\Link',

For example, if you want to use your own SELECT command, just write:

$commands = array(
  'select' => "My\Library\Command\Select"
);

$query = new Query(array(), $commands);

Some commands have a pre-defined interface: if so, bare in mind that is better that your custom command implements the original command’s interface.