...
| 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);
// FetchBuild anythe valuesSQL set fromfor the subquery
$valuessub_query
$sub_query_sql = $sub_query_record->values>get();
// Fetch Buildany thevalues SQLset forfrom the subquery sub_query
$sub_query_sql
$values = $sub_query_record->get()>values; |
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($values)->from("users")->innerJoin(array($sub_query_sql=>"temp"), "temp.user_id", "=", "users.id", false); |
...
| 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), false); |
Miscellaneous Select Statements
...
| Code Block |
|---|
|
// Executes CREATE TABLE `feed_reader_feeds` (
// `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
// `url` varchar(255) NOT NULL,
// `updated` datetime DEFAULT NULL,
// PRIMARY KEY (`id`),
// UNIQUE KEY `url` (`url`)
// ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
$this->Record->
setField("id", array('type' => "int",'size' => 10,'unsigned' => true,'auto_increment' => true))->
setField("url", array('type' => "varchar", 'size' => 255))->
setField("updated", array('type' => "datetime", 'is_null' => true, 'default' => null))->
setKey(array("id"), "primary")->
setKey(array("url"), "unique")->
create("feed_reader_feeds"); |
Alter
...
Drop
...
|
// Executes ALTER TABLE `feed_reader_feeds`
// ADD `title` varchar(255) NOT NULL,
// DROP UNIQUE KEY `url`
$this->Record->
setField("title", array('type' => "varchar", 'size' => 255))->
setKey(array("url"), "unique", null, false)->
alter("feed_reader_feeds"); |
Truncate
| Code Block |
|---|
|
// Executes TRUNCATE TABLE `feed_reader_feeds`
$this->Record->truncate("feed_reader_feeds"); |
Drop
| Code Block |
|---|
|
// Executes DROP TABLE `feed_reader_feeds`
$this->Record->drop("feed_reader_feeds"); |
Transactions
Transactions offer a method of executing queries in series, with the ability to roll back to a previous state if an error occurs. Any database error will throw a PDOException.
...