Query builder
Preface
The query builder is a class which is used to build up a query for execution later. For example if you need multiple wheres for a query you can chain them together on this QueryBuilder
class. The class is then modified until you want to execute the query. Models use the query builder under the hood to make all of those calls. Many model methods actually return an instance of QueryBuilder
so you can continue to chain complex queries together.
Using the query builder class directly allows you to make database calls without needing to use a model.
Getting the QueryBuilder class
To get the query builder class you can simply import the query builder. Once imported you will need to pass the connection_details
dictionary you store in your config.database
file:
You can also switch or specify connection on the fly using the on
method:
from_("users")
is also a valid alias for thetable("users")
method. Feel free to use whatever you feel is more expressive.
You can then start making any number of database calls.
Models
If you would like to use models you should reference the Models documentation. This is an example of using models directly with the query builder.
By default, the query builder will return dictionaries or lists depending on the result set. Here is an example of a result using only the query builder:
Fetching Records
Select
You can also select a table and column:
You can also select a table and an asterisk (*
). This is useful when doing joins:
Lastly you can also provide the column with an alias by adding as
to the column select:
Select Distinct
You can also select distinct records by simple adding a distinct()
method onto the query builder.
First
You can easily get the first record:
All Records
You can also simply fetch all records from a table:
The Get Method
Once you start chaining methods you should call the get()
method instead of the all()
method to execute the query.
For example, this is correct:
And this is wrong:
Wheres
You may also specify any one of these where statements:
The simplest one is a "where equals" statement. This is a query to get where username
equals Joe
AND age
equals 18
:
You can also use a dictionary to build the where method:
You can also specify different comparison operators:
Where Null
Another common where clause is checking where a value is NULL
:
This will fetch all records where the admin column is NULL
.
Or the inverse:
This selects all columns where admin is NOT NULL
.
Where In
In order to fetch all records within a certain list we can pass in a list:
This will fetch all records where the age is either 18
, 21
or 25
.
Where Like
You can do a WHERE LIKE or WHERE NOT LIKE query:
Where Subqueries
You can make subqueries easily by passing a callable into the where method:
You can also so a subquery for a where_in
statement:
Select Subqueries
You can make a subquery in the select clause. This takes 2 parameters. The first is the alias for the subquery and the second is a callable that takes a query builder.
This will add a subquery in the select part of the query. You can then order by or perform wheres on this alias.
Here is an example of all stores that make more than 1000 in sales:
Conditional Queries
Sometimes you need to specify conditional statements and run queries based on the conditional values.
For example you may have code that looks like this:
Instead of writing the code above you can use the when
method. This method accepts a conditional as the first parameter and a callable as the second parameter. The code above would look like this:
If the conditional passed in the first parameter is not truthy then the second parameter will be ignored.
Limits / Offsets
It's also very simple to use both limit and/or offset a query.
Here is an example of a limit:
Here is an example of an offset:
Or here is an example of using both:
Between
You may need to get all records where column values are between 2 values:
Group By
You may want to group by a specific column:
You can also specify a multiple column group by:
Group By Raw
You can also group by raw:
Having
Having clauses are typically used during a group by. For example, returning all users grouped by salary where the salary is greater than 0:
You may also specify the same query but where the sum of the salary is greater than 50,000
Joining
Creating join queries is very simple.
This will build a JoinClause
behind the scenes for you.
Advanced Joins
Advanced joins are for use cases where you need to compile a join clause that is more than just joining on 2 distant columns. Advanced joins are where you need additional on
or where statements
.There are currently 2 ways to perform an advanced where clause.
The first way is that you may create your own JoinClause
from scratch and build up your own clause:
The second way is passing a "lambda" to the join method directly which will return you a JoinClause
class you can build up. This way is a bit more cleaner:
Left Join
and a right join:
Right Join
Increment
There are times where you really just need to increment a column and don't need to pull any additional information. A lot of the incrementing logic is hidden away:
Decrementing is also similiar:
Decrement
You also pass a second parameter for the number to increment the column by.
Pagination
Sometimes you'll want to paginate through a result set. There are 2 ways to pagainate records.
The first is a "length aware" pagination. This means that there will be additional results on the pagination like the total records. This will do 2 queries. The initial query to get the records and a COUNT query to get the total. For large or complex result sets this may not be the best choice as 2 queries will need to be made.
You may also do "simple pagination". This will not give you back a query total and will not make the second COUNT query.
Aggregates
There are several aggregating methods you can use to aggregate columns:
Sum
Notice the alias for the aggregate is the name of the column.
Average
Notice the alias for the aggregate is the name of the column.
Count
You can also count all:
Max
Min
Aliases
You may also specify an alias for your aggregate expressions. You can do this by adding "as {alias}" to your aggregate expression:
Order By
You can easily order by:
The default is ascending order but you can change directions:
You can also specify a comma separated list of columns to order by all 3 columns:
You may also specify the sort direction on each one individually:
This will sort name
and active
in ascending order because it is the default but will sort email in descending order.
These 2 peices of code are the same:
Order By Raw
You can also order by raw. This will pass your raw query directly to the query:
Creating Records
You can create records by passing a dictionary to the create
method. This will perform an INSERT query:
Bulk Creating
You can also bulk create records by passing a list of dictionaries:
Raw Queries
If some queries would be easier written raw you can easily do so for both selects and wheres:
You can also specify a fully raw query using the statement
method. This will simply execute a query directly and return the result rather than building up a query:
You can also pass query bindings as well:
You can also use the Raw
expression class to specify a raw expression. This can be used with the update query:
You can also query using having raw. This will pass your raw query directly to the query:
Chunking
If you need to loop over a lot of results then consider chunking. A chunk will only pull in the specified number of records into a generator:
Getting SQL
If you want to find out the SQL that will run when the command is executed. You can use to_sql()
. This method returns the full query without bindings. The actual query sent to the database is a "qmark query" (see below). This to_sql()
method is mainly for debugging purposes and should not be sent directly to a database as the result with have no query bindings and will be subject to SQL injection attacks. Use this method for debugging purposes only.
Getting Qmark
Qmark is essentially just a normal SQL statement except that the query is replaced with quoted question marks ('?'
). The values that should have been in the position of the question marks are stored in a tuple and sent along with the qmark query to help in sql injection. The qmark query is the actual query sent using the connection class.
Note: qmark queries will reset the query builder and remove things like aggregates and wheres from the builder class. Because of this, writing
get()
afterto_qmark
will result in incorrect queries (because things like wheres and aggregates will be missing from the final query). If you need to debug a query, please use theto_sql()
method which does not have this kind of resetting behavior.
Updates
Updating Records
You can update many records.
Deletes
Deleting Records
You can delete many records as well. For example, deleting all records where active is set to 0.
Truncating
You can also truncate directly from the query builder:
You may also temporarily disable and re-enable foreign keys to avoid foreign key checks.
Available Methods
Aggregates
Method | Description |
---|---|
.avg('column') | Gets the average of a column. Can also use an |
.sum('column') | Gets the sum of a column. Can also use an |
.count('column') | Gets the count of a column. Can also use an |
.max('column') | Gets the max value of a column. Can also use an |
.min('column') | Gets the min value of a column. Can also use an |
.distinct() | Makes the query a SELECT DISTINCT query. |
Joins
Method | Description |
---|---|
.join('table1', 'table2.id', '=', 'table1.table_id') | Joins 2 tables together. This will do an INNER join. Can control which join is performed using the |
.left_join('table1', 'table2.id', '=', 'table1.table_id') | Joins 2 tables together. This will do an LEFT join. |
.right_join('table1', 'table2.id', '=', 'table1.table_id') | Joins 2 tables together. This will do an RIGHT join. |
Where Clauses
Method | Description |
---|---|
.between('column', 'value') | Peforms a BETWEEN clause. |
.between('column', 'value') | Peforms a BETWEEN clause. |
.not_between('column', 'value') | Peforms a NOT BETWEEN clause. |
.where('column', 'value') | Peforms a WHERE clause. Can optionally choose a logical operator to use |
.or_where('column', 'value') | Peforms a OR WHERE clause. Can optionally choose a logical operator to use |
.where_like('column', 'value') | Peforms a WHERE LIKE clause. |
.where_not_like('column', 'value') | Peforms a WHERE NOT LIKE clause. |
.where_exists(lambda q: q.where(..)) | Peforms an EXISTS clause. Takes a lambda expression to indicate which subquery should generate. |
.where_not_exists(lambda q: q.where(..)) | Peforms a NOT EXISTS clause. Takes a lambda expression to indicate which subquery should generate. |
.where_column('column1', 'column2') | Peforms a comparison between 2 columns. Logical operators available include: |
.where_in('column1', [1,2,3]) | Peforms a WHERE IN clause. Second parameter needs to be a list or collection of values. |
.where_not_in('column1', [1,2,3]) | Peforms a WHERE NOT IN clause. Second parameter needs to be a list or collection of values. |
.where_null('column1') | Peforms a WHERE NULL clause. |
.where_not_null('column1') | Peforms a WHERE NOT NULL clause. |
Pessimistic Locking
The query builder includes a few functions to help you do “pessimistic locking” on your SELECT statements.
To run the SELECT statement with a “shared lock”, you may use the shared_lock method on a query:
To “lock for update” on a SELECT statement, you may use the lock_for_update method on a query:
Raw Queries
Method | Description |
---|---|
.select_raw('SUM("column")') | specifies a raw string where the select expression would go. |
.where_raw('SUM("column")') | specifies a raw string where the WHERE expression would go. |
.having_raw('SUM("column") > 10') | specifies a raw string where the HAVING expression would go. |
.order_by_raw('column1, column2') | specifies a raw string where the ORDER BY expression would go. |
.group_by_raw('column1, column2') | specifies a raw string where the GROUP BY expression would go. |
Modifiers
Method | Description |
---|---|
.limit('10') | Limits the results to 10 rows |
.offset(10) | Offsets the results by 10 rows |
.take(10) | Alias for the |
.skip(10) | Alias for the |
.group_by('column') | Adds a GROUP BY clause. |
.having('column') | Adds a HAVING clause. |
.increment('column') | Increments the column by 1. Can pass in a second parameter for the number to increment by. |
.decrement('column') | Decrements the column by 1. Can pass in a second parameter for the number to increment by. |
DML
Method | Description |
---|---|
.add_select("alias", lambda q: q.where(..)) | Performs a SELECT subquery expession. |
.all() | Gets all records. |
.chunk(100) | Chunks a result set. Uses a generator to keep each chunk small. Useful for chunking large data sets where pulling too many results in memory will overload the application |
.create({}) | Limits the results to 10 rows. Must take a dictionary of values. |
.delete() | Performs a DELETE query based on the current clauses already chained onto the query builder. |
.first() | Gets the first record |
.from_('users') | Sets the table. |
.get() | Gets all records. Used in combination with other builder methods to finally execute the query. |
.last() | Gets the last record |
.paginate(limit, page) | Paginates a result set. Pass in different pages to get different results. This a length aware pagination. This will perform a COUNT query in addition to the original query. Could be slower on larger data sets. |
.select('column') | Offsets the results by 10 rows. Can use the |
.simple_paginate(limit, page) | Paginates a result set. Pass in different pages to get different results. This not a length aware pagination. The result will not contain the total result counts |
.statement("select * from users") | Performs a raw query. |
.table('users') | Alias for the |
.truncate('table') | Truncates a table. Can pass a second parameter to disable and enable foreign key constraints. |
.update({}) | dictionary values to update the record with. |
Testing
Method | Description |
---|---|
.to_sql() | Returns a string of the fully compiled SQL to be generated. |
.to_qmark('') | Returns a string of the SQL to generated but with |
Low Level Methods
These are lower level methods that may be useful:
Method | Description |
---|---|
.new() | Creates a new clean builder instance. This instance does not have any clauses, selects, limits, etc from the original builder instance. Great for performing subqueries |
.where_from_builder() | Creates a WHERE clause from a builder instance. |
.get_table_name() | Gets the tables name. |
Last updated