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.connection_details
dictionary you store in your config.database
file:on
method:from_("users")
is also a valid alias for thetable("users")
method. Feel free to use whatever you feel is more expressive.
*
). This is useful when doing joins:as
to the column select:get()
method instead of the all()
method to execute the query.username
equals Joe
AND age
equals 18
:NULL
:NULL
.NOT NULL
.18
, 21
or 25
.where_in
statement: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:JoinClause
behind the scenes for you.on
or where statements
.There are currently 2 ways to perform an advanced where clause.JoinClause
from scratch and build up your own clause:JoinClause
class you can build up. This way is a bit more cleaner:name
and active
in ascending order because it is the default but will sort email in descending order.create
method. This will perform an INSERT query:statement
method. This will simply execute a query directly and return the result rather than building up a query:Raw
expression class to specify a raw expression. This can be used with the update query: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.'?'
). 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, writingget()
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.
as
modifier to alias the .avg('column as alias')
.as
modifier to alias the .sum('column as alias')
.as
modifier to alias the .count('column as alias')
.as
modifier to alias the .max('column as alias')
.as
modifier to alias the .min('column as alias')
.clause
parmeter. Can choose inner
, left
or right
..where('column', '=', 'value')
. Logical operators available include: <
, >
, >=
, <=
, !=
, =
, like
, not like
.where('column', '=', 'value')
. Logical operators available include: <
, >
, >=
, <=
, !=
, =
, like
, not like
<
, >
, >=
, <=
, !=
, =
limit
methodoffset
method.increment('column', 100)
..decrement('column', 100)
.as
keyword to alias the column. .select('column as alias')
from_
method.truncate('table', foreign_keys=True)
?
values where the sql bindings are placed. Also resets the query builder instance.