Query Builder

Note: for the sake of brevity, any time you see $query without a definition it equals tr_query()->table('wp_posts')->setIdColumn('ID')

Query Builder

To create an SQL statements create a new instance of the Query class. To make a custom query object instance the \Typerocket\Database\Query class.

$query = new \Typerocket\Database\Query();


$query = tr_query();

Use Table

$query = tr_query()->table('wp_posts');

Get ID and Set Primary Key

The ID column is not always id. You can set and get it via the $idColumn property accessor methods.

$query = tr_query()->table('wp_posts')->setIdColumn('ID');
$idColumn = tr_query()->getIdColumn();
echo $idColumn;




There are four find methods models can use: findById, findAll, findOrDie, and findFirstWhereOrDie. For the majority of the following section we will use the wp_posts table as the example and work with the following $query object:

$query = tr_query()->table('wp_posts')->setIdColumn('ID');

To get one item by its primary column ID:


To get a list of all items:


To get a list of all items with specific IDs:


To get one item by its primary column ID or die with wp_die:


To get the first item by where the version is v4 or die with wp_die:

$query->findFirstWhereOrDie('post_title', 'Hello world!');


The get() method queries the database for select calls.

$result = $query->select('post_title', 'ID')->take(10)->get();


You can use the select() method get return specific columns only.

$query->select('ID', 'title')>first();

Or, every column.



There are two where methods for models where and orWhere. These methods can be chained any way you like.

$query->where('post_title', '!=' ,'Hello world!')->orWhere('id', 10);


$query->where('post_title', '!=' ,'Hello world!')->where('id', 2);

Where In

$query->where('id', 'IN', [1,2,3]);

Where Not In

$query->where('id', 'NOT IN', [1,2,3]);

Raw Where

At times you need to inject raw information into your where statements. You can do this by using the appendRawWhere() method. For example:

$query = new \TypeRocket\Database\Query();
        $query->idColumn = 'ID';
        $query->run = false;
            ->appendRawWhere('AND', "post_status = 'publish'")
            ->appendRawWhere('OR', "(post_title = 'Hello World!' AND ID = 1)")
            ->update(['post_title' => 'My Title']);

Will output,

UPDATE wp_posts SET post_title='My Title' WHERE post_status = 'publish' OR (post_title = 'Hello World!' AND ID = 1)

Take: Limit and Offset

To take a certain scope of items use the take() method. This method takes two arguments:

  1. $limit - The number of items to take.
  2. $offset - The number of items to offset.
$result = $query->findAll()->take(10, 10)->get();

Order By

You can set the ordering of results with orderBy(). this method takes two arguments:

  1. $column - The column to order by, primary key is the default.
  2. $direction - The direction or the ordering ASC (default) and DESC.
$result = $query->findAll()->orderBy('post_title', 'DESC')->get();

Group By

You can set the grouping of results with groupBy(). this method takes one argument:

  1. $column - The column to group by.
$result = $query->findAll()->groupBy('ID')->get();

Date Time

In some cases you will want to get the current date time for the MySQL database. You can use the getDateTime() method to access the current time in MySQL format.

$time = $query->getDateTime();


You can create records like so.

    'post_id' => 1,
    'meta_key' => 'key',
    'meta_value' => 'value'


You can update records like so.

$data = [
    'post_id' => 1,
    'meta_key' => 'key',
    'meta_value' => 'value'



If your query has one result like with findById() you can delete it.


You can also bulk delete items.



To return only unique results use the distinct() method.



Joins are powerful tools for merging tables. Here we join the wp_posts table with wp_postmeta. There are three join methods: join(), leftJoin(), and rightJoin().


The join() method takes 5 arguments.

  1. $table - The table to be queried.
  2. $column - A column to join on.
  3. $arg1 - The second column to join on. Or, the operator (=, !=, >, <, ...).
  4. $arg2 (optional) - The second column and $arg1 must be an operator.
  5. $type(optional) - The type of join: INNER, LEFT, and RIGHT.

Here is an example join query.

$query = tr_query()->table('wp_posts');
$query->select('wp_posts.post_title', 'wp_posts.ID', 'wp_postmeta.meta_key')
    ->join('wp_postmeta', 'wp_postmeta.post_id', '=', 'wp_posts.ID', 'INNER')
    ->where('wp_posts.ID', 1)

The shorthand with the same result,

$query = tr_query()->table('wp_posts');
$query->select('wp_posts.post_title', 'wp_posts.ID', 'wp_postmeta.meta_key')
    ->join('wp_postmeta', 'wp_postmeta.post_id', 'wp_posts.ID')
    ->where('wp_posts.ID', 1)

Left and right Joins

The leftJoin() and rightJoin() methods work in the same way as the join() method, except they do not have a fifth argument. Here is a left join in shorthand.

$query = tr_query()->table('wp_posts');
$query->select('wp_posts.post_title', 'wp_posts.ID', 'wp_postmeta.meta_key')
    ->leftJoin('wp_postmeta', 'wp_postmeta.post_id', 'wp_posts.ID')
    ->where('wp_posts.ID', 1)


You can combine two tables results as well with the union() method.

$last = tr_query()->table('wp_posts')->setIdColumn('ID');
$last->select('post_title', 'ID')
              ->where('ID', 2);

$first = tr_query()->table('wp_posts')->setIdColumn('ID');
$posts = $first->select('post_title', 'ID')
              ->where('ID', 1)
              ->union($last) // union


You can do some basic math function calls as well.


You can run the count() method on a model to get the number of items.

$var = $query->count();


You can run the min() method to get the min of a column.

$var = $query->min('ID');


You can run the max() method to get the max of a column.

$var = $query->max('ID');


You can run the avg() method to get the average of a column.

$var = $query->avg('ID');


You can run the sum() method to get the sum of a column.

$var = $query->sum('ID');