Query Builder

1. # Query Builder
2. # Use Table
3. # Get ID and Set Primary Key
4. # Finding
5. # Get
6. # Select
7. # Where
8. # Where In
9. # Where Not In
10. # Raw Where
11. # Take: Limit and Offset
12. # Order By
13. # Group By
14. # Date Time
15. # Create
16. # Update
17. # Delete
18. # Distinct
19. # Joins
20. # Join
21. # Left and right Joins
22. # Union
23. # Functions
24. # Count
25. # Min
26. # Max
27. # Avg
28. # Sum

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

Or,

$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;

Outputs,

ID

#Finding

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:

$query->findById(1);

To get a list of all items:

$query->findAll()->get();

To get a list of all items with specific IDs:

$query->findAll([1,2,3])->get();

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

$query->findOrDie(1);

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

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

#Get

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

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

#Select

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

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

Or, every column.

$query->select()->first();

#Where

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

And,

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

#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->table('wp_posts');
        $query->idColumn = 'ID';
        $query->run = false;
        $query
            ->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();

#Create

You can create records like so.

tr_query()->table('wp_postmeta')->setIdColumn('meta_id')->create([
    'post_id' => 1,
    'meta_key' => 'key',
    'meta_value' => 'value'
]);

#Update

You can update records like so.

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

tr_query()->table('wp_postmeta')
          ->setIdColumn('meta_id')
          ->findById(1)
          ->update($data);

#Delete

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

$query->findById(1)->delete();

You can also bulk delete items.

$query->delete([1,2,3]);

#Distinct

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

$query->distinct()->get();

#Joins

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

#Join

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->setIdColumn('ID');
$query->select('wp_posts.post_title', 'wp_posts.ID', 'wp_postmeta.meta_key')
    ->distinct()
    ->join('wp_postmeta', 'wp_postmeta.post_id', '=', 'wp_posts.ID', 'INNER')
    ->where('wp_posts.ID', 1)
    ->get();

The shorthand with the same result,

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

#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->setIdColumn('ID');
$query->select('wp_posts.post_title', 'wp_posts.ID', 'wp_postmeta.meta_key')
    ->distinct()
    ->leftJoin('wp_postmeta', 'wp_postmeta.post_id', 'wp_posts.ID')
    ->where('wp_posts.ID', 1)
    ->get();

#Union

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

#Functions

You can do some basic math function calls as well.

#Count

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

$var = $query->count();

#Min

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

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

#Max

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

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

#Avg

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

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

#Sum

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

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