Database Conditionals

To create a conditional using the Global Database class:

$conditional = new \Database\Conditional($db_object, $left_side, $right_side, $operator);

In our first example, we are going to skip using the above and instead use the createConditional method:

$db = \Database::newDB();
$tbl = $db->addTable('foo');
$id_field = $tbl->getField('id');
$conditional = $db->createConditional($id_field, 1, '=');
$db->setConditional($conditonal);
$result = $db->select();

Let's break this down.

First create the DB object

$db = \Database::newDB();

Next we add a table to the query and grab the result.

$tbl = $db->addTable('foo');

I am grabbing a field from the table. This makes sure that the LEFT side of my conditional is a table column name.

$id_field = $tbl->getField('id');

I am using getField here instead of addField. addField adds the column as a requested query result. If addField is not called, then SELECT * is used by default.

Next we create our Conditional:

$conditional = $db->createConditional($id_field, 1, '=');

Here I am setting the id field to equal 1. I don't have to stop here. I could start nesting conditionals:

// example nesting
$conditional2 = $db->createConditional($tbl->getField('bar'), 'alpha', '=');
$nested_conditional = $db->createConditional($conditional, $conditional2, 'and');

You can pair conditionals up as much as you like. In the end, you will just have one conditional that you set in the DB object.

$db->setConditional($conditional);
$result = $db->select();

The above insures you get the exact conditional statement you need but there are some shortcuts.

If you want a Conditional based on a table field comparison:

$operator =  '='; // or >, <, in, not in, etc.
$conditional = $table_object->getFieldConditional('column_name', 'comparison_value', $operator);

If you know all your conditionals are going to be compared with 'AND', you can 'add' them to the database object instead of using 'set';

$db_object->addConditional($conditional1);
$db_object->addConditional($conditional2);

This would be the same as:

$db_object->setConditional($db_object->createConditional($conditional1, $conditional2, 'and'));

There is also a table field shortcut for adding conditionals.

$db = \Database::newDB();
$db->addTable('foo')->addFieldConditional('id', 1);
// The above replaces the commented below:
//$id_field = $tbl->getField('id');
//$conditional = $db->createConditional($id_field, 1, '=');
//$db->setConditional($conditonal);
$result = $db->select();

If you want to use a subselect:

$db = Database::newDB();
$alpha = $db->addTable('alpha');
$alpha->addField('id');
$ss = new \Database\SubSelect($db, 't1');

$db2 = Database::newDB();
$beta = $db2->addTable('beta');

$c1 = $beta->getFieldConditional('alpha_id', $ss, 'in');
$db2->setConditional($c1);

echo $db2->selectQuery();

The result:

SELECT `beta`.* 
FROM   `beta` 
WHERE (`beta`.`alpha_id` 
      IN (SELECT `alpha`.`id` FROM `alpha`))