Documentation
Database: Query Builder
- # Introduction
- # Retrieving Records
- # Selects
- # Using Aggregate Methods
- # Using Where Clause
- # Using OrderBy, GroupBy, Limit & Offset, Having Clause
- # Using Joins
- # Using Alias
- # Creating A Record
- # Updating A Record
- # Deleting A Record
Introduction
The database Query Builder provides a simple fluent interface for creating and running queries without writing SQL. It just mean you can chain method calls together, one after another. This makes your code more readable and ease to use.
Cygnite enable you to use Query Builder two different way, use model object or use \Cygnite\Database\DB class to build and executing queries. If you are using DB class than you may start with a call to the table static method. This tells the query builder which table to use when making the query. Finally, the chain is finished by calling either findOne() or findMany() or (findAll()), which executes the query and returns the Collection object.
Before getting started, make sure to configure a database connection in src/Apps/Configs/database.php file. For more information on database configuration read documentation.
Retrieving Records
Retrieving Multiple Records
To start building query you may use "table" static method, which will return instance of fluent query builder for the table. Allowing you to chain methods and retrieve records. Below example to retrieve all records from the table.
use Cygnite\Database\DB;
DB::table('person')->findMany();
Retrieving A Single Record
If you like to retrieve a single record from database, you may use findOne() or first() method. The findOne() method will return Cygnite\Database\ResultSet instance whereas first() returns Cygnite\Foundation\Collection instance.
DB::table('person')->where('name', '=', 'Tom Cruise')->findOne();
DB::table('person')->where('name', '=', 'Tom Cruise')->first();
The above code snippet is equivalent of writing.
#sql => SELECT `product` .* FROM `product` WHERE name = 'Tom Cruise';
To find a single record by ID, you may pass ID directly to the findOne() or use where clause in first() method.
$person = DB::table('person')->where('id', '=', 5)->findOne();
$person = DB::table('person')->where('id', '=', 5)->first();
[The primary key of the record - Cygnite query builder assumes the primary key column is called 'id' by default but this is configurable.]
As Array
By default findOne() method returns results as array, using first() or findMany() method always returns Collection instance. You can convert result set as array by chaining asArray() method.
$person = DB::table('person')->where('id', '=', 5)->first()->asArray();
$person = DB::table('person')->findMany()->asArray();
As JSON
To returning result set as JSON object chain asJson() method on findMany().
$person = DB::table('person')->findMany()->asJson();
Counting Results
To return count of number of records use count() method chaining on the findMany() method.
$person = DB::table('person')->findMany()->count();
Selects
You many not wish to select all columns from database, use select() method to specify columns to be retrieved.
$person = DB::table('person')->select("name, email_address as email")->findMany();
You may wish to apply DISTINCT to construct the query, use select() method but alias name for the column.
$person = DB::table('person')->select("DISTINCT category as category")->findMany();
Select Raw Expressions
The fluent query builder easily identifies whether you are trying to construct raw expression or selecting normal columns just by aliasing the column name in select() method. You may use selectExpr() method for raw expression in the query.
$person = DB::table('person')->select("count(*) as total_persons")->findMany();
$person = DB::table('person')->selectExpr("DISTINCT category as category, status")
->where('status', '!=', 0)->findMany();
Aggregates Functions
The fluent Query builder provides various aggregate functions, such as sum, avg, count, max, min etc. Use select() or selectExpr() method to construct your query.
$product = DB::table('product')->select("sum(price) as total")->findMany();
$product = DB::table('product')->select("max(price) as max_price")->findMany();
$product = DB::table('product')->select("min(price) as min_price")->findMany();
$product = DB::table('product')->select("avg(price) as average")->findMany();
Using Where Clause
The fluent query builder provides a single where() filter method for constructing various types of conditions. But you may use other filter method based on your requirement. All the WHERE clauses will be ANDed together when the query is run.
$person = DB::table('person')->select("name, type, address")
->where('status', '=', 1)
->where('country_code', 'LIKE', 'USA')
->findMany();
You may also use variety of other conditional operator in the where clause.
$person = DB::table('person')->where('created_at', '>=', '2015-11-24 05:20:00')
->where('updated_at', '<=', date('Y-m-d H:m:s'))
->findMany();
$person = DB::table('person')->where('name', 'like', '%Fabin')
->findMany();
$person = DB::table('person')->where('status', '<>', 0)
->findMany();
Using Where In Clause
The whereIn() method construct a query that the column value in array values.
$person = DB::table('person')->whereIn('id', [3,7,8,15])
->findMany();
Using OR Where Clause
You may chain orWhere() with where clause.
$person = DB::table('person')->where('name', '=', 'Sunny')
->orWhere('name', '=', 'Shane')
->findMany();
Using OR Where In Clause
You may chain orWhereIn() with where clause to construct query.
$person = DB::table('person')->where('id', '=', 72)
->orWhereIn('id', [7, 8, 9])
->findMany();
Using Where IS NULL/IS NOT NULL
To add WHERE column IS NULL or WHERE column IS NOT NULL in the query use whereRaw() method.
$person = DB::table('person')->whereRaw('designation IS NULL')
->findMany();
$person = DB::table('person')->whereRaw('designation IS NOT NULL')
->findMany();
Using OrderBy
The orderBy method allows you to sort results by column. Method aspects first parameter as column name and second parameter is sort type either asc or desc
$person = DB::table('person')->orderBy('id', 'desc')->findMany();
$person = DB::table('person')->orderBy('id', 'asc')->findMany();
Using Group By
To add GROUP BY clause in your query use groupBy method, aspects first parameter as column name. You may also pass multiple columns for grouping.
$person = DB::table('person')->where('type', '=', 'male')
->groupBy('name')
->findMany();
$person = DB::table('person')->where('type', '=', 'female')
->groupBy(['id', 'name'])
->findMany();
Using Limit Clause
To limit the number of records returned by the query you may use limit() method. You may pass first parameter to limit to limit results and second parameter as offset.
$person = DB::table('person')->where('type', '=', 'male')
->limit(3)
->findMany(); // will fetch first 3 records from database
$person = DB::table('person')->where('type', '=', 'male')
->limit(10, 5)
->findMany();
Using Having Clause
To use aggregate functions in combination with a GROUP BY you can use HAVING to filter those values. The having() method works exactly same as where() method.
$person = DB::table('person')->groupBy('id')
->having('id', '>', 2)
->findMany();
$person = DB::table('person')->groupBy('id')
->having('id', '>', 2)
->orHaving('id', '<', 8)
->findMany();
$person = DB::table('person')->groupBy('id')
->havingRaw("category is not null")
->findMany();
Using JOIN
Active Record allow you to query against database using multiple join conditions. Active Record uses fluent query builder to build join query for your table. Various join supported by active record are JOIN, LEFT JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN and raw JOIN queries.
$products = DB::table('product')->select('product.name, category.id')
->join('category', ['category.id', '=', 'product.category_id'])
->where('product.id', '>', '295')
->orderBy('product.id', 'DESC')
->limit(10)
->findMany();
Using LEFT JOIN
$products = DB::table('product')->select('product.name, category.id')
->leftJoin('category', ['category.id', '=', 'product.category_id'])
->findMany();
Using INNER JOIN
$products = DB::table('product')->select('product.name, category.id')
->innerJoin('category', ['category.id', '=', 'product.category_id'])
->findMany();
Using LEFT OUTER JOIN
$products = DB::table('product')->select('product.name, category.id')
->leftOuterJoin('category', ['category.id', '=', 'product.category_id'])
->findMany();
Using RIGHT OUTER JOIN
$products = DB::table('product')->select('product.name, category.id')
->rightOuterJoin('category', ['category.id', '=', 'product.category_id'])
->findMany();
Using Raw JOIN Queries
//->rawJoin('raw query', ['table1.id', '=', 'table2.id'], 'alias name');
$products = DB::table('product')->select('product.name, category.id')
->rawJoin(
'JOIN (SELECT * FROM category WHERE category.id = $id)',
['p.id', '=', 'c.id'], 'c'
)->findMany();
JOIN Query Using Table Alias
$products = DB::table('product')->select('p.name, c.id')
->tableAlias('as p')
->join('category', ['p.id', '=', 'c.id'] , 'c')
->where('p.id', '>', '295')
->groupBy(array('p.type'))
->orderBy('p.id', 'DESC')
->limit(10)
->findMany();
Creating A Record
The query builder provides insert() method to create a new record. You may pass column value pair as an array into insert method.
DB::table('product')->insert(['name' => 'Iphone', 'category' => 'Mobile']);
Getting Last Inserted Id
$product = DB::table('product');
$product->insert(['name' => 'Iphone', 'category' => 'Mobile']);
show($product->id());
Updating Record In A Table
The query builder also provides update() method to update any record in a database.
DB::table('product')->where('id', '=', 7)
->update(['name' => 'Macbook Pro', 'category' => 'Laptop']);
DB::table('product')->update(['name' => 'Macbook Pro', 'category' => 'Laptop'], ['id' => 7]);
Deleting Records
You may delete record from table using trash() method.
DB::table('product')->where('id', '=', 5)->trash();
DB::table('product')->trash(5);
Deleting multiple records from the Table
DB::table('product')->trash([3,7,8], true);
