For instance you may only want to apply a where statement if a given input value is present on the incoming request. This guide is a reference to all the available methods on the Query Builder.
Sometimes you may need to create more advanced where clauses such as "where exists" clauses or nested parameter groupings. This is critical when building a query involving joins and one of the desired columns is found in multiple tables.
The updateOrInsert method will first attempt to locate a matching database record using the first argument's column and value pairs. If one of the tables has a row with a join field value that is not found in the other table, that row would not be included in the result. Because the Count function is only concerned with counting the number of rows associated with each grouping, it doesn't really matter which field you plug into the parentheses.
Be aware that the second argument allows expressions and is not safe for user-input:. To perform a basic "inner join", you may use the join method on a query builder instance. In the first queries we wrote, we pulled data from a single table, like so:.
We'll account for this problem using a sub-query later in the lesson. If you would like to perform a "left join" or "right join" instead of an "inner join", use the leftJoin or rightJoin methods. This method will return the value of the column directly:. You end up supporting a "simplified grammar. Including the parent table eliminates any confusion the SQL interpreter would have in deciding which column should be retrieved.
Updates All update operations return the number of affected rows: Aggregates The query builder also provides a variety of aggregate methods such as count , max , min , avg , and sum.
If any of your queries throws an error, the transaction rolls back automatically, otherwise, it is committed.
To get started, let's look at an example of grouping constraints within parenthesis: Instead of using DB:: Untested on any other database platform. Skip to main content. The whereYear method may be used to compare a column's value against a specific year:.
WHERE clause syntax. In this example, we'll retrieve a Collection of role titles: You might also look at Perl's DBIx:: Pagination Query Builder provides convenient methods to paginate database results. Cross joins generate a cartesian product between the first table and the joined table:. The having method's signature is similar to that of the where method: