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")

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 `username` from `users`

First

You can easily get the first record:

builder.table('users').first()
# SELECT `username` 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)

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()

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()

Inner Joining

Joining is a way to take data from related tables and return it in 1 result set as well as filter anything out that doesn't have a relationship on the joining tables.

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

This join will create an inner join.

You can also choose a left join:

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')

Aggregates

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

Sum

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

Average

builder.table('users').avg('salary').get()

Count

builder.table('users').count('salary').get()

Max

builder.table('users').max('salary').get()

Min

builder.table('users').min('salary').get()

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()

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 and is not the query that gets sent to the database. The query sent to the database is a "qmark query". This to_sql() method is mainly for debugging purposes.

See the section below for more information on qmark queries.

builder.table('users').count('salary').to_sql()
#== SELECT COUNT(`users`.`salary`) FROM `users`

Getting Qmark

Qmark is essentially just a normal SQL statement except the query is replaced with 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_sql()
#== SELECT COUNT(`users`.`salary`) FROM `users` WHERE `users`.`age` = '?'

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()

Available Methods

aggregate	all	between
count	create	decrement
delete	first	get
group_by	having	increment
join	left_join	limit
max	not_between	offset
order_by	right_join	select
select_raw	sum	to_qmark
to_sql	update	where
where_column	where_exists	where_has
where_in	where_not_in	where_not_null
where_null	where_raw