...
| Code Block |
|---|
|
// Produces SELECT `id`,`id_code` AS `key`,`user_id` FROM `clients`
$this->Record->select(array("id","id_code"=>"key","user_id"))->from("clients"); |
Tuple
Limit
| Code Block |
|---|
|
// Produces SELECT * FROM `clients` LIMIT 20, 10
$this->Record->select()->from("clients")->limit(10, 20); |
...
| Code Block |
|---|
|
// Produces SELECT `clients`.* FROM `clients` LEFT JOIN `contacts` ON `contacts`.`user_id`=`clients`.`user_id` AND `contacts`.`id`!='0'
$this->Record->select(array("clients.*"))->from("clients")->on("contacts.id", "!=", 0)->leftJoin("contacts", "contacts.user_id", "=", "clients.user_id", false);
// Produces SELECT `clients`.* FROM `clients` LEFT JOIN `contacts` ON `contacts`.`user_id`=`clients`.`user_id` OR `contacts`.`id`!='0'
$this->Record->select(array("clients.*"))->from("clients")->orOn("contacts.id", "!=", 0)->leftJoin("contacts", "contacts.user_id", "=", "clients.user_id", false); |
Subqueries
All subqueries start first with the subquery. The idea is to construct the query from the inside out, and as each layer is added the subquery becomes part of the parent query.
| Code Block |
|---|
|
$client_id = 12;
// Produces SELECT `clients`.*, `contacts`.* FROM `clients` INNER JOIN `contacts` ON `contacts`.`client_id`=`clients`.`id` WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`
$sub_query_record = new Record();
$sub_query_record->select(array("clients.*","contacts.*"))->from("clients")->
innerJoin("contacts", "contacts.client_id", "=", "clients.id", false)->
where("clients.id", "=", $client_id)->
where("contacts.user_id", "=", "clients.user_id", false);
// Fetch any values set from the subquery
$values = $sub_query_record->values;
// Build the SQL for the sub_query
$sub_query_sql = $sub_query_record->get(); |
Join on Subquery
| Code Block |
|---|
|
// Produces SELECT `users`.* FROM `users` INNER JOIN (SELECT `clients`.*, `contacts`.* FROM
// `clients` INNER JOIN `contacts` ON `contacts`.`client_id`=`clients`.`id` WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`)
// AS `temp` ON `temp`.`user_id`=`users`.`id`
$this->Record->select("users.*")->appendValues($value)->from("users")->innerJoin(array($sub_query_sql=>"temp"), "temp.user_id", "=", "users.id", false); |
...
Subquery as a Tuple
| Code Block |
|---|
|
// Produces SELECT `temp`.`id` FROM (SELECT `clients`.*, `contacts`.* FROM
// `clients` INNER JOIN `contacts` ON `contacts`.`client_id`=`clients`.`id` WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`)
// AS `temp` WHERE `temp`.`id`='125'
$this->Record->select("temp.id")->appendValues($value)->from(array($sub_query=>"temp"))->where("temp.id","=",125); |
Subquery as a Column
| Code Block |
|---|
|
// Produces SELECT `clients`.`id`, (SELECT `clients`.*, `contacts`.* FROM `clients` INNER JOIN `contacts` ON `contacts`.`client_id`=`clients`.`id`
// WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`) AS `temp` FROM `clients` WHERE `clients`.`id`='125'
$this->Record->select("clients.id")->appendValues($value)->select(array("(" . $sub_query . ")"=>"temp"), false)->from("clients")->where("clients.id","=",125); |
Where not in Subquery
| Code Block |
|---|
|
// Produces SELECT * FROM `clients` WHERE `id` NOT IN (SELECT `clients`.*, `contacts`.* FROM `clients` INNER JOIN
// `contacts` ON `contacts`.`client_id`=`clients`.`id` WHERE `clients`.`id`='12' AND `contacts`.`user_id`=`clients`.`user_id`)
$this->Record->select()->from("clients")->where("id", "notin", array($sub_query)) |
Miscellaneous Select Statements
Result Count
| Code Block |
|---|
|
$this->Record->select()->from("clients")->numResults(); |
Last Insert ID
| Code Block |
|---|
|
$this->Record->lastInsertId(); |
Raw SQL
| Code Block |
|---|
|
// Select with single parameter
$this->Record->query("SELECT `clients`.* FROM `clients` WHERE `clients`.`id`=?", 12);
// Select with multiple parameters
$this->Record->query("SELECT `clients`.* FROM `clients` WHERE `clients`.`id`=? OR `clients`.`id`=?", 12, 15);
// Select with array of parameters
$this->Record->query("SELECT `clients`.* FROM `clients` WHERE `clients`.`id`=? OR `clients`.`id`=?", array(12, 15)); |
Insert
Update
Delete
Table Management
...