I'll cover query optimization in Chapter 4, "Query Optimization." Join Types Instead, the PostgreSQL query optimizer analyzes the query and plans an execution strategy to minimize execution time. In practice, this would be a poor strategy: Cross-joins can get very large quickly. Next, PostgreSQL could throw out all the rows that fail to satisfy the WHERE clause. To execute this query, PostgreSQL could start by creating the cross-join between all the tables involved, producing an intermediate result table. Movies-# WHERE customers.id = rentals.customer_id Now you can construct a query that will show us all of the rentals and the names of the corresponding customers: Adding the table qualifier is required if a command involves two columns with identical names, but is useful in other cases. So, customers.id refers to the id column in the customers table and rentals.customer_id refers to the customer_id column in the rentals table. You may find it helpful to qualify each column name by prefixing it with the name of the corresponding table, followed by a period. Notice that this WHERE clause mentions two columns with similar names ( customer_id and id). Looking back at the previous query, you can see that the meaningful rows are those WHERE customers.id = rentals.customer_id. So, given a rentals row, we can find the corresponding customers row by searching for a customer where the customer ID is equal to rentals.customer_id. Each row in the customers table is uniquely identified by a customer ID. What is the real-world relationship between a rentals row and a customers row? Each row in the rentals table contains a customer ID. These seven rows were produced by joining the first row in the rentals table: Take a close look at the results of the previous query. An inner-join starts with a cross-join, and then throws out the rows that you don't want. The second type of join, the inner-join, is very useful. The result set contains 4 x 7 or 28 rows.Ĭross-joins are rarely useful?they usually don't represent real-world relationships. The rentals table contains four rows the customers table contains seven rows. You can see that PostgreSQL has joined each row in the rentals table to each row in the customers table. Tape_id | rental_date | customer_id | id | customer_nameĪB-12345 | | 1 | 4 | Wonderland, Alice N.ĪB-67472 | | 3 | 4 | Wonderland, Alice N. Movies=# SELECT rentals.*, customers.id, customers.customer_name To perform a cross-join, we simply list each table in the FROM clause: Id | customer_name | phone | birth_date | balance We'll cross-join the rentals and customers tables. If you are joining against a third table, PostgreSQL joins each row in the intermediate result with each row in the third table. In a cross join, PostgreSQL joins each row in the first table to each row in the second table to produce a result table. The most basic join type is a cross-join (or Cartesian product). PostgreSQL (and all relational databases) supports a number of join types. When you need to retrieve data from multiple tables, you join those tables. If you SELECT from the rentals table, you can see the tape ID and customer ID, but you can't see the movie title or customer name. You can see that each row in the rentals table refers to a tape ( tape_id) and to a customer ( customer_id). ![]() Notice that each row in the rentals table refers to a customer ( customer_id) and a tape ( tape_id)." There are three pieces of information that we need to record for each rental: the tape_id, the customer_id, and the date that the rental occurred. "When a customer comes in to rent a tape, we will add a row to the rentals table to record the transaction. Here's a description of the rentals table from earlier in this chapter: Most databases contain multiple tables and there are relationships between these tables. You may use the table method provided by the DB facade to begin a query.So far, all the queries that you've seen involve a single table. Therefore, you should never allow user input to dictate the column names referenced by your queries, including "order by" columns. ![]() PDO does not support binding column names. There is no need to clean or sanitize strings passed to the query builder as query bindings. The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks. It can be used to perform most database operations in your application and works perfectly with all of Laravel's supported database systems. Laravel's database query builder provides a convenient, fluent interface to creating and running database queries.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |