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:

from masoniteorm.query import QueryBuilder

builder = QueryBuilder().table("users")

You can also switch or specify connection on the fly using the on method:

from masoniteorm.query import QueryBuilder

builder = QueryBuilder().on('staging').table("users")

from_("users") is also a valid alias for the table("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:

# Without models
user = QueryBuilder().table("users").first()
# == {"id": 1, "name": "Joe" ...}

# With models
from masoniteorm.models import Model

class User(Model):
    pass

user = QueryBuilder(model=User).table("users").first()
# == <app.models.User>

Fetching Records

Select

builder.table('users').select('username').get()
# SELECT `users`.`username` FROM `users`

You can also select a table and column:

builder.table('users').select('profiles.name').get()
# SELECT `profiles`.`name` FROM `users`

You can also select a table and an asterisk (*). This is useful when doing joins:

builder.table('users').select('profiles.*').get()
# SELECT `profiles`.* FROM `users`

Lastly you can also provide the column with an alias by adding as to the column select:

builder.table('users').select('profiles.username as name').get()
# SELECT `profiles`.`username` AS name FROM `users`

First

You can easily get the first record:

builder.table('users').first()
# SELECT * from `users` LIMIT 1

All Records

You can also simply fetch all records from a table:

builder.table('users').all()
# SELECT * from `users`

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:

builder.table('users').select('username').get()

And this is wrong:

builder.table('users').select('username').all()

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:

builder.table('users').where('username', 'Joe').where('age', 18).get()

You can also use a dictionary to build the where method:

builder.table('users').where({"username": "Joe", "age": 18}).get()

You can also specify comparison operators:

builder.table('users').where('age', '=', 18).get()
builder.table('users').where('age', '>', 18).get()
builder.table('users').where('age', '<', 18).get()
builder.table('users').where('age', '>=', 18).get()
builder.table('users').where('age', '<=', 18).get()

Where Null

Another common where clause is checking where a value is NULL:

builder.table('users').where_null('admin').get()

This will fetch all records where the admin column is NULL.

Or the inverse:

builder.table('users').where_not_null('admin').get()

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:

builder.table('users').where_in('age', [18,21,25]).get()

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:

builder.table('users').where_like('name', "Jo%").get()
builder.table('users').where_not_like('name', "Jo%").get()

Subqueries

You can make subqueries easily by passing a callable into the where method:

builder.table("users").where(lambda q: q.where("active", 1).where_null("activated_at")).get()
# SELECT * FROM "users" WHERE ("users"."active" = '1' AND "users"."activated_at" IS NULL)

You can also so a subquery for a where_in statement:

builder.table("users").where_in("id", lambda q: q.select("profile_id").table("profiles")).get()
# SELECT * FROM "users" WHERE "id" IN (SELECT "profiles"."profile_id" FROM "profiles")

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.

builder.table("stores").add_select("sales", lambda query: (
    query.count("*").from_("sales").where_column("sales.store_id", "stores.id")
)).order_by("sales", "desc")

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:

builder.table("stores").add_select("sales", lambda query: (
    query.count("*").from_("sales").where_column("sales.store_id", "stores.id")
)).where("sales", ">", "1000")

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:

def show(self, request: Request):
    age = request.input('age')
    article = Article.where('active', 1)
    if age >= 21:
        article.where('age_restricted', 1)

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:

def show(self, request: Request):
    age = request.input('age')
    article = Article.where('active', 1).when(age >= 21, lambda q: q.where('age_restricted', 1))

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:

builder.table('users').limit(10).get()

Here is an example of an offset:

builder.table('users').offset(10).get()

Or here is an example of using both:

builder.table('users').limit(10).offset(10).get()

Between

You may need to get all records where column values are between 2 values:

builder.table('users').where_between('age', 18, 21).get()

Group By

You may want to group by a specific column:

builder.table('users').group_by('active').get()

You can also specify a multiple column group by:

builder.table('users').group_by('active, name, is_admin').get()

Group By Raw

You can also group by raw:

builder.table('users').group_by_raw('COUNT(*)').get()

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:

builder.table('users').sum('salary').group_by('salary').having('salary').get()

You may also specify the same query but where the sum of the salary is greater than 50,000

builder.table('users').sum('salary').group_by('salary').having('salary', 50000).get()

Joining

Creating join queries is very simple.

builder.join('other_table', 'column1', '=', 'column2')

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:

from masoniteorm.expressions import JoinClause

clause = (
    JoinClause('other_table as ot')
    .on('column1', '=', 'column2')
    .on('column3', '=', 'column4')
    .where('column3', '>', 4)
)

builder.join(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:

builder.join('other_table as ot', lambda join: (
    (
        join.on('column1', '=', 'column2')
        .on('column3', '=', 'column4')
        .where('column3', '>', 4)
    )
))

Left Join

builder.table('users').left_join('table1', 'table2.id', '=', 'table1.table_id')

and a right join:

Right Join

builder.table('users').right_join('table1', 'table2.id', '=', 'table1.table_id')

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:

builder.table('users').increment('status')

Decrementing is also similiar:

Decrement

builder.table('users').decrement('status')

You also pass a second parameter for the number to increment the column by.

builder.table('users').increment('status', 10)
builder.table('users').decrement('status', 10)

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.

builder.table("users").where("active", 1).paginate(number_of_results, page)

You may also do "simple pagination". This will not give you back a query total and will not make the second COUNT query.

builder.table("users").where("active", 1).simple_paginate(number_of_results, page)

Aggregates

There are several aggregating methods you can use to aggregate columns:

Sum

salary = builder.table('users').sum('salary').first().salary

Notice the alias for the aggregate is the name of the column.

Average

salary = builder.table('users').avg('salary').first().salary

Notice the alias for the aggregate is the name of the column.

Count

salary = builder.table('users').count('salary').first().salary

You can also count all:

salary = builder.table('users').count('salary').first().salary

Max

salary = builder.table('users').max('salary').first().salary

Min

salary = builder.table('users').min('salary').first().salary

Aliases

You may also specify an alias for your aggregate expressions. You can do this by adding "as {alias}" to your aggregate expression:

builder.table('users').sum('salary as payments').get()
#== SELECT SUM(`users`.`salary`) as payments FROM `users`

Order By

You can easily order by:

builder.order_by("column")

The default is ascending order but you can change directions:

builder.order_by("column", "desc")

You can also specify a comma separated list of columns to order by all 3 columns:

builder.order_by("name, email, active")

You may also specify the sort direction on each one individually:

builder.order_by("name, email desc, active")

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:

builder.order_by("name, active").order_by("name", "desc")
builder.order_by("name, email desc, active")

Order By Raw

You can also order by raw. This will pass your raw query directly to the query:

builder.order_by_raw("name asc")

Creating Records

You can create records by passing a dictionary to the create method. This will perform an INSERT query:

builder.create({"name": "Joe", "active": 1})

Bulk Creating

You can also bulk create records by passing a list of dictionaries:

builder.bulk_create([
    {"name": "Joe", "active": 1},
    {"name": "John", "active": 0},
    {"name": "Bill", "active": 1},
])

Raw Queries

If some queries would be easier written raw you can easily do so for both selects and wheres:

builder.table('users').select_raw("COUNT(`username`) as username").where_raw("`username` = 'Joe'").get()

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:

builder.statement("select count(*) from users where active = 1")

You can also pass query bindings as well:

builder.statement("select count(*) from users where active = '?'", [1])

You can also use the Raw expression class to specify a raw expression. This can be used with the update query:

from masoniteorm.expressions import Raw

builder.update({
    "name": Raw('"alias"')
})
# == UPDATE "users" SET "name" = "alias"

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:

for users in builder.table('users').chunk(100):
    for user in users:
        user #== <User object>

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.

builder.table('users').count('salary').where('age', 18).to_sql()
#== SELECT COUNT(`users`.`salary`) AS salary FROM `users` WHERE `users`.`age` = '18'

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.

builder.table('users').count('salary').where('age', 18).to_qmark()
#== SELECT COUNT(`users`.`salary`) AS salary FROM `users` WHERE `users`.`age` = '?'

Note: qmark queries will reset the query builder and remove things like aggregates and wheres from the builder class. Because of this, writing get() after to_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 the to_sql() method which does not have this kind of resetting behavior.

Updates

Updating Records

You can update many records.

builder.where('active', 0).update({
    'active': 1
})
# UPDATE `users` SET `users`.`active` = 1 where `users`.`active` = 0

Deletes

Deleting Records

You can delete many records as well. For example, deleting all records where active is set to 0.

builder.where('active', 0).delete()

Truncating

You can also truncate directly from the query builder:

builder.truncate('users')

You may also temporarily disable and re-enable foreign keys to avoid foreign key checks.

builder.truncate('users', foreign_keys=True)

Available Methods

Aggregates

Joins

Where Clauses

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:

builder.where('votes', '>', 100).shared_lock().get()

To “lock for update” on a SELECT statement, you may use the lock_for_update method on a query:

builder.where('votes', '>', 100).lock_for_update().get()

Raw Queries

Modifiers

DML

Testing

Low Level Methods

These are lower level methods that may be useful: