Masonite Essentials

Query Builder

🧩 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 masonite.orm.builder import QueryBuilder
from config.database import CONNECTIONS
builder = QueryBuilder(connection_details=CONNECTIONS)

You can then start making any number of database calls.

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

The Get Method

Anytime you need to get records you call the get() method instead of the all() method.

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 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 the 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.

Limits / Offsets

It's also very simple to both limit 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()

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

You may update records as well.

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

Section In Development

  • where

  • where_has

  • first

  • update

  • create

  • set_scope

  • set_global_scope

  • select

  • select_raw

  • create

  • delete

  • where

  • where_raw

  • or_where[str, int, callable])

  • where_exists"QueryBuilder"]):

  • having

  • where_null

  • where_not_null

  • between

  • not_between

  • where_in

  • where_not_in

  • join

  • left_join

  • right_join

  • where_column

  • limit

  • offset

  • update

  • increment

  • decrement

  • sum

  • count

  • max

  • order_by

  • group_by

  • aggregate

  • first

  • all

  • get

  • get_grammar

  • to_sql

  • to_qmark

  • new