Database: Query Builder

Introduction

Kiaan's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works perfectly with all of Kiaan's supported database systems.

The Kiaan query builder uses PDO parameter binding to protect your application against SQL injection attacks.

There is no need to clean or sanitize strings passed to the query builder as query bindings.

Methods

Table

You may use the table method to select a table:

DB::table('table');
# Output: "SELECT * FROM table"

DB::table('table1, table2');
# Output: "SELECT * FROM table1, table2"

DB::table('table1 AS t1, table2 AS t2');
# Output: "SELECT * FROM table1 AS t1, table2 AS t2"

and you can use array parameter:

DB::table(['table1', 'table2']);
# Output: "SELECT * FROM table1, table2"

DB::table(['table1 AS t1', 'table2 AS t2']);
# Output: "SELECT * FROM table1 AS t1, table2 AS t2"

Retrieving All Rows From A Table

You may use the table method provided by the DB facade to begin a query. The table method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the $user = DB::table('users')->where('name', 'Hassan')->first();

return $user->email; method:

$users = DB::table('users')->all();
$users = DB::table('users')->get();

foreach ($users as $user) {
    echo $user->name;
}

To retrieve all data, use the data method:

DB::table('users')->get()->data();

Retrieving A Single Row

If you just need to retrieve a single row from a database table, you may use the DB facade's first method. This method will return a single stdClass object:

$user = DB::table('users')->where('name', 'John')->first();
 
return $user->email;

If you don't need an entire row, you may extract a single value from a record using the value method. This method will return the value of the column directly:

DB::table('users')->get()->value('id')

// or

DB::table('users')->first()->value('id')

To retrieve a single row by its id column value, use the find method:

$user = DB::table('users')->find(3);

to change id key , use the keymethod:

$user = DB::table('comment')->key('user_id')->find(1);

Aggregates

The query builder also provides a variety of methods for retrieving aggregate values like count, max, min, avg, and sum. You may call any of these methods after constructing your query:

# Count
DB::table('users')->count();
DB::table('users')->get()->length();
DB::table('users')->first()->length();

# Max
DB::table('users')->max('price');

# Min
DB::table('users')->min('price');

# Avg
DB::table('users')->avg('price');

# Sum
DB::table('users')->sum('price');

Determining If Records Exist

Instead of using the count method to determine if any records exist that match your query's constraints, you may use the existsmethod:

if (DB::table('orders')->where('finalized', 1)->exists()) {
    // ...
}

Select Statements

You may not always want to select all columns from a database table.

Using the select method, you can specify a custom "select" clause for the query:

$users = DB::table('users')
            ->select('name', 'email as user_email')
            ->get();
            
// Or
$users = DB::table('users')
            ->select(['name', 'email as user_email'])
            ->get();     
// Or
$users = DB::table('users')
            ->select('name')
            ->select('email')
            ->get();

The distinct method allows you to force the query to return distinct results:

DB::table('users')->distinct()->get();

DB::table('users')->select('email')->distinct()->get();

Joins

You can use this method in 7 ways. These;

• join

• leftJoin

• rightJoin

• innerJoin

• fullOuterJoin

• leftOuterJoin

• rightOuterJoin

Examples:

DB::table('test as t')->leftJoin('foo as f', 't.id', 'f.t_id')->get();
# Output: "SELECT * FROM test as t LEFT JOIN foo as f ON t.id=f.t_id"

DB::table('test as t')->fullOuterJoin('foo as f', 't.id', 'f.t_id')->get();
# Output: "SELECT * FROM test as t FULL OUTER JOIN foo as f ON t.id=f.t_id"

Where Clauses

You can use this method in 6 ways. These;

• where

• orWhere

• notWhere

• orNotWhere

• whereNull

• whereNotNull

Where Clauses

You may use the query builder's where method to add "where" clauses to the query. The most basic call to the where method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.

For example, the following query retrieves users where the value of the votes column is equal to 100 and the value of the age column is greater than 35:

$users = DB::table('users')
         ->where('votes', '=', 100) 
         ->where('age', '>', 35) 
         ->get();

For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where method. Kiaan will assume you would like to use the = operator:

$users = DB::table('users')->where('votes', 100)->get();

and other example:

DB::table('test')->where('age = ? OR age = ?', [18, 20])->get();

$users = DB::table('users')
                ->where('votes', '>=', 100)
                ->get();
 
$users = DB::table('users')
                ->where('votes', '<>', 100)
                ->get();
 
$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();

You may also pass an array of conditions to the where function. Each element of the array should be an array containing the three arguments typically passed to the where method:

$db->table('test')->where([
	'name' => 'Hassan',
	'age' => 24,
	'status' => 1
        ])->get();

Or Where Clauses

When chaining together calls to the query builder's where method, the "where" clauses will be joined together using the and operator. However, you may use the orWhere method to join a clause to the query using the or operator. The orWhere method accepts the same arguments as the where method:

$users = DB::table('users')
 ->where('votes', '>', 100)
  ->orWhere('name', 'Hassan')
   ->get();

If you need to group an "or" condition within parentheses, you may pass a closure as the first argument to the grouped method:

DB::table('users')
	->grouped(function($q) {
		$q->where('country', 'Egypt')->orWhere('country', 'TURKEY');
	})
	->where('status', 1)
	->get();
# Ouput: "SELECT * FROM users WHERE (country='Egypt' OR country='TURKEY') AND status ='1'"

Where Not Clauses

The whereNot and orWhereNot methods may be used to negate a given group of query constraints. For example, the following query excludes products that are on clearance or which have a price that is less than ten:

DB::table('test')->where('active', 1)->notWhere('auth', 1)->get();

Additional Where Clauses

whereNull / whereNotNull

The whereNull method verifies that the value of the given column is NULL:

$users = DB::table('users') 
             ->whereNull('updated_at') 
             ->get();

The whereNotNull method verifies that the column's value is not NULL:

$users = DB::table('users')
                ->whereNotNull('updated_at')
                ->get();

whereId

When id is equal to a value.

$users = DB::table('users')
                ->whereId(1)
                ->first();

In

You can use this method in 4 ways. These;

• in

• orIn

• notIn

• orNotIn

The in method verifies that a given column's value is contained within the given array:

DB::table('test')->where('active', 1)->in('id', [1, 2, 3])->get();
# Output: "SELECT * FROM test WHERE active = '1' AND id IN ('1', '2', '3')"

The notInmethod verifies that the given column's value is not contained in the given array:

DB::table('test')->where('active', 1)->notIn('id', [1, 2, 3])->get();
# Output: "SELECT * FROM test WHERE active = '1' AND id NOT IN ('1', '2', '3')"

findInSet

DB::table('test')->where('active', 1)->findInSet('selected_ids', 1)->get();
# Output: "SELECT * FROM test WHERE active = '1' AND FIND_IN_SET (1, selected_ids)"

You can use this method in 4 ways. These;

• findInSet

• orFindInSet

• notFindInSet

• orNotFindInSet

Example:

DB::table('test')->where('active', 1)->notFindInSet('selected_ids', 1)->get();
# Output: "SELECT * FROM test WHERE active = '1' AND NOT FIND_IN_SET (1, selected_ids)"

# OR

DB::table('test')->where('active', 1)->orFindInSet('selected_ids', 1)->get();
# Output: "SELECT * FROM test WHERE active = '1' OR FIND_IN_SET (1, selected_ids)"

Between

between / orBetween

The between method verifies that a column's value is between two values:

DB::table('test')->where('active', 1)->between('age', 18, 25)->get();

notBetween / orNotBetween

The notBetween method verifies that a column's value lies outside of two values:

DB::table('test')->where('active', 1)->notBetween('age', 18, 25)->get();

Like

You can use like method for search.

DB::table('test')->like('title', "%php%")->get();
# Output: "SELECT * FROM test WHERE title LIKE '%php%'"

You can use this method in 4 ways. These;

• like

• orLike

• notLike

• orNotLike

Example:

DB::table('test')->where('active', 1)->notLike('tags', '%dot-net%')->get();
# Output: "SELECT * FROM test WHERE active = '1' AND tags NOT LIKE '%dot-net%'"

# OR

DB::table('test')->like('bio', '%php%')->orLike('bio', '%java%')->get();
# Output: "SELECT * FROM test WHERE bio LIKE '%php%' OR bio LIKE '%java%'"

Grouping

The groupBy & having Methods

As you might expect, the groupBy and having methods may be used to group the query results. The having method's signature is similar to that of the where method:

# Usage 1: Array parameter
DB::table('test')->where('status', 1)->groupBy(['cat_id', 'user_id'])->get();
# Output: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id, user_id"

You can use the having method to filter the results:

DB::table('test')->where('status', 1)->groupBy('city')->having('COUNT(person)', 100)->get();
# Output: "SELECT * FROM test WHERE status='1' GROUP BY city HAVING COUNT(person) > '100'"

# OR

DB::table('test')->where('active', 1)->groupBy('department_id')->having('AVG(salary)', '<=', 500)->get();
# Output: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) <= '500'"

# OR

DB::table('test')->where('active', 1)->groupBy('department_id')->having('AVG(salary) > ? AND MAX(salary) < ?', [250, 1000])->get();
# Output: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) > '250' AND MAX(salary) < '1000'"

orderBy

The orderBy method is used to sort the result-set in ascending or descending order.

# Usage 1: One parameter
DB::table('test')->where('status', 1)->orderBy('id')->get();
DB::table('test')->where('status', 1)->idAsc()->get();
# Output: "SELECT * FROM test WHERE status='1' ORDER BY id ASC"

# OR

DB::table('test')->where('status', 1)->orderBy('id desc')->get();
DB::table('test')->where('status', 1)->idDesc()->get();
# Output: "SELECT * FROM test WHERE status='1' ORDER BY id desc"

# Usage 2: Two parameters
DB::table('test')->where('status', 1)->orderBy('id', 'desc')->get();
DB::table('test')->where('status', 1)->idDesc()->get();
# Output: "SELECT * FROM test WHERE status='1' ORDER BY id DESC"

# Usage 3: Rand()
DB::table('test')->where('status', 1)->orderBy('rand()')->limit(10)->get();
DB::table('test')->where('status', 1)->orderByRand()->limit(10)->get();
# Output: "SELECT * FROM test WHERE status='1' ORDER BY rand() LIMIT 10"

Limit & Offset

You may use the limit and offset methods to limit the number of results returned from the query or to skip a given number of results in the query:

# Usage 1: One parameter
DB::table('test')->limit(10)->get();
# Output: "SELECT * FROM test LIMIT 10"

# Usage 2: Two parameters
DB::table('test')->limit(10, 20)->get();
# Output: "SELECT * FROM test LIMIT 10, 20"

# Usage 3: with offset method
DB::table('test')->limit(10)->offset(10)->get();
# Output: "SELECT * FROM test LIMIT 10 OFFSET 10"

Insert Statements

The query builder also provides an insert method that may be used to insert records into the database table. The insert method accepts an array of column names and values:

DB::table('users')->insert([ 
   'email' => '[e-mail]', 
   'votes' => 0 
]);

You may insert several records at once by passing an array of arrays. Each array represents a record that should be inserted into the table:

DB::table('users')->insert([
    ['email' => '[e-mail]', 'votes' => 0],
    ['email' => '[e-mail]', 'votes' => 0],
]);

// OR
$users = DB::table("users");
$users->name = 'omar';
$users->email = '[e-mail]';
$users->insert();

Auto-Incrementing IDs

If the table has an auto-incrementing id, use the lastId method to get a last insert ID:

$lastId = DB::lastId();

Or

$lastId = DB::table('users')->insert([ 
            'email' => '[e-mail]', 
            'votes' => 0 
           ]);

Update Statements

In addition to inserting records into the database, the query builder can also update existing records using the update method. The update method, like the insert method, accepts an array of column and value pairs indicating the columns to be updated. The update method returns the number of affected rows. You may constrain the update query using where clauses:

DB::table('users') 
    ->where('id', 1) 
    ->update(['votes' => 1]);
    
// Or
$users = DB::table("users")->first();
$users->name = 'ali';
$users->update();

Find Or Fail

Takes an id and returns a single model. If no matching model exist, it throws an error.

DB::table("users")->findOrFail(1)

First or Fail

Returns the first record found in the database. If no matching model exist, it throws an error

DB::table("users")->firstOrFail()

First Or

Sometime, you need to write logic for default value. when you find some product from database and it's not match any record then you can return default product. so you have to write long logic behind this but eloquent provide firstOr() where you can easily return default object.

$category = Category::where("name", "Mobile2")->firstOr(function () {
    return Category::where("name", "Laptop")->first();
});  

First Or Insert

It tries to find a model matching the attributes you pass in the first parameter. If a model is not found, it automatically creates and saves a new Model after applying any attributes passed in the second parameter:

DB::table('users')->firstOrInsert([
    'name' => "name",
    'email' => "email"
]);

Update Or Insert

Sometimes you may want to update an existing record in the database or create it if no matching record exists. In this scenario, the updateOrInsert method may be used. The updateOrInsert method accepts two arguments: an array of conditions by which to find the record, and an array of column and value pairs indicating the columns to be updated.

The updateOrInsert method will attempt to locate a matching database record using the first argument's column and value pairs. If the record exists, it will be updated with the values in the second argument. If the record can not be found, a new record will be inserted with the merged attributes of both arguments:

DB::table('users')
    ->updateOrInsert(
        ['email' => '[e-mail]', 'name' => 'Hassan'],
        ['votes' => '2']
    );

Increment & Decrement

The query builder also provides convenient methods for incrementing or decrementing the value of a given column. Both of these methods accept at least one argument: the column to modify. A second argument may be provided to specify the amount by which the column should be incremented or decremented:

DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);

DB::table('users')->update(["votes" => "value * 2"], true);

Delete

The query builder's delete method may be used to delete records from the table. The delete method returns the number of affected rows. You may constrain delete statements by adding "where" clauses before calling the delete method:

$deleted = DB::table('users')->truncate();
$deleted = DB::table('users')->delete();
 
$deleted = DB::table('users')->where('votes', '>', 100)->delete();

Auto Increment

Get next auto increment number for table.

DB::table('users')->nextId();

Query execute

To query execute use query method:

# Usage 1: Select all records
DB::query('SELECT * FROM test WHERE id=? AND status=?', [10, 1])->fetchAll();

# Usage 2: Select one record
DB::query('SELECT * FROM test WHERE id=? AND status=?', [10, 1])->fetch();

# Usage 3: Other queries like Update, Insert, Delete etc...
DB::query('DELETE FROM test WHERE id=?', [10])->exec();