Database Select
The select() method it is based on database mapping, see Database Map.
When innerJoin()-ing, or leftJoin()-ing tables, it is not necessary to specify tables relationship keys, because they are already defined on Database Map.
Joining tables are also based on previous chained table relationship definition.
If the join is based on more than one chained table behind, you must send a second argument called viaTable.
innerAssoc() and leftAssoc() acts like joins, but they are used for pivoting many to many relationships, which uses a third table. There is no need to include the pivot table on query building. Just specify the main tables. Like for simple joins, if the "join assoc" type is based on more than one chained table behind, you must send a second argument called viaTable.
See the next examples. They are based on sample definition from the Database Map.
If the methods argument is just the table name, it should not be enclosed in the backticks.
The custom() method allows you to add more to the query.
The final method should be query(), which will return the SQL string. After this you can query the database, using this string. See Database available methods.
You should bind only string parameters. Other scalar parameters should be escaped using floatval(), intval() and boolval().
Few complex examples:
<?php
$query = $this->db
->select("`houses`.*")
->from("houses")
->innerJoin("streets")
->innerJoin("addresses")
->innerJoin("authors")
->where("`authors`.`name` = :name")
->groupBy("`authors`.`id`")
->having("`authors`.`id` > ".intval($this->proxy->post('id')))
->orderBy("`authors`.`name` ASC")
->limit("0, 15")
->custom("FOR UPDATE")
->query()
;
d($this->db->getAll($query, [':name' => $this->proxy->post('name')]));
?>
<?php
$query = $this->db
->select("`books`.*, `profiles`.*, `addresses`.*")
->from("books")
->innerAssoc("authors")
->innerJoin("profiles")
->innerJoin("addresses", viaTable: "authors")
->where("`authors`.`salary` < ".floatval($this->proxy->post('salary')))
->groupBy("`authors`.`id`")
->having("`authors`.`id` > 0")
->orderBy("`authors`.`name` ASC")
->limit("0, 15")
->custom("FOR UPDATE")
->query()
;
dd($this->db->getAll($query));
?>
<?php
$query = $this->db
->select("`profiles`.*")
->from("authors")
->leftJoin("profiles")
->leftJoin("addresses", viaTable: "authors")
->where("`authors`.`id` = ".intval($this->proxy->get('id')))
->groupBy("`authors`.`id`")
->having("`authors`.`name` = :name")
->orderBy("`authors`.`name` ASC")
->limit("0, 15")
->custom("FOR UPDATE")
->query()
;
d($this->db->getAll($query, [':name' => $this->proxy->post('name')]));
?>