...
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 sub_query $sub_query_sqlsubquery $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
...