...
| 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$values)->from("users")->innerJoin(array($sub_query_sql=>"temp"), "temp.user_id", "=", "users.id", false); |
...
| 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$values)->from(array($sub_query_sql=>"temp"))->where("temp.id","=",125); |
...
| 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$values)->select(array("(" . $sub_query_sql . ")"=>"temp"), false)->from("clients")->where("clients.id","=",125); |
...
| 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")->appendValues($values)->where("id", "notin", array($sub_query_sql)) |
Miscellaneous Select Statements
...
| Code Block | ||
|---|---|---|
| ||
$this->Record->lastInsertId(); |
...
Custom 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
Simple Insert
| Code Block | ||
|---|---|---|
| ||
// Executes INSERT INTO `clients` (`user_id`,`id_code`) VALUES ('15','USR15')
$this->Record->insert("clients", array('user_id' => 15,'id_code' => "USR15"));
// Executes INSERT INTO `clients` (`user_id`,`id_code`) VALUES ('15','USR15')
$this->Record->set("id_code","USR15")->set("user_id", 15)->insert("clients"); |
Insert with Filter
| Code Block | ||
|---|---|---|
| ||
// Executes INSERT INTO `clients` (`user_id`,`id_code`) VALUES ('15','USR15')
$this->Record->insert("clients", array('user_id' => 15,'id_code' => "USR15", 'first_name' => "First Name", 'last_name' => "Last Name"), array("user_id","id_code")); |
On Duplicate
| Code Block | ||
|---|---|---|
| ||
// Executes INSERT INTO `clients` (`user_id`,`id_code`) VALUES ('15','USR15') ON DUPLICATE KEY UPDATE `id_code`='USR15'
$this->Record->duplicate("id_code", "=", "USR15")->insert("clients", array('user_id' => 15,'id_code' => "USR15")); |
From Subquery
| Code Block | ||
|---|---|---|
| ||
$sub_query_record = new Record();
$sub_query_record->select(array("clients.id"))->from("clients")->where("clients.id", "=", 12);
// Executes INSERT INTO `some_table` (`value`) (SELECT `clients`.`id` FROM `clients` WHERE `clients`.`id`='12')
$this->Record->insert("some_table", array('value'=>$sub_query_record)); |
Update
Delete
Table Management
...