Introduction The Lyger Query Builder provides a fluent interface for building and executing database queries. It’s inspired by Laravel’s Query Builder and offers a clean, readable syntax for working with databases.
Basic Usage Creating a Query Builder Instance You can create a QueryBuilder instance in two ways:
use Lyger\Database\ QueryBuilder ; // Using the static table method $users = QueryBuilder :: table ( 'users' ) -> get (); // Using the constructor $query = new QueryBuilder ( 'users' ); $users = $query -> get ();
Select Queries Retrieving All Records $users = QueryBuilder :: table ( 'users' ) -> get (); foreach ( $users as $user ) { echo $user [ 'name' ]; }
Selecting Specific Columns $users = QueryBuilder :: table ( 'users' ) -> select ([ 'id' , 'name' , 'email' ]) -> get ();
Getting a Single Record // Get first matching record $user = QueryBuilder :: table ( 'users' ) -> where ( 'id' , '=' , 1 ) -> first (); // Get a single column value $email = QueryBuilder :: table ( 'users' ) -> where ( 'id' , '=' , 1 ) -> value ( 'email' );
The first() method returns null if no record is found, while value() returns null if the column doesn’t exist.
Where Clauses Basic Where Clauses // Basic where with operator $users = QueryBuilder :: table ( 'users' ) -> where ( 'status' , '=' , 'active' ) -> get (); // Shorthand (defaults to '=' operator) $users = QueryBuilder :: table ( 'users' ) -> where ( 'status' , 'active' ) -> get (); // Multiple where conditions $users = QueryBuilder :: table ( 'users' ) -> where ( 'status' , '=' , 'active' ) -> where ( 'role' , '=' , 'admin' ) -> get ();
Or Where Clauses $users = QueryBuilder :: table ( 'users' ) -> where ( 'status' , '=' , 'active' ) -> orWhere ( 'role' , '=' , 'admin' ) -> get ();
Where In Clauses $users = QueryBuilder :: table ( 'users' ) -> whereIn ( 'id' , [ 1 , 2 , 3 , 4 , 5 ]) -> get ();
Null Checks // Where column is NULL $users = QueryBuilder :: table ( 'users' ) -> whereNull ( 'deleted_at' ) -> get (); // Where column is NOT NULL $users = QueryBuilder :: table ( 'users' ) -> whereNotNull ( 'email_verified_at' ) -> get ();
Ordering Order By // Ascending order (default) $users = QueryBuilder :: table ( 'users' ) -> orderBy ( 'name' , 'ASC' ) -> get (); // Descending order $users = QueryBuilder :: table ( 'users' ) -> orderBy ( 'created_at' , 'DESC' ) -> get ();
Latest and Oldest Convenient methods for ordering by timestamps:
// Order by created_at DESC $users = QueryBuilder :: table ( 'users' ) -> latest () -> get (); // Order by created_at ASC $users = QueryBuilder :: table ( 'users' ) -> oldest () -> get (); // Use a different column $users = QueryBuilder :: table ( 'posts' ) -> latest ( 'published_at' ) -> get ();
Limiting and Offset Limit // Get first 10 records $users = QueryBuilder :: table ( 'users' ) -> limit ( 10 ) -> get ();
Offset // Skip first 10 records, get next 10 $users = QueryBuilder :: table ( 'users' ) -> offset ( 10 ) -> limit ( 10 ) -> get ();
The paginate() method provides automatic pagination:
$result = QueryBuilder :: table ( 'users' ) -> where ( 'status' , '=' , 'active' ) -> paginate ( 15 , 1 ); // 15 per page, page 1 // Result structure: // [ // 'data' => [...], // Array of records // 'current_page' => 1, // 'per_page' => 15, // 'total' => 150, // 'last_page' => 10, // 'from' => 1, // 'to' => 15 // ]
Number of records per page
Joins Inner Join $users = QueryBuilder :: table ( 'users' ) -> join ( 'posts' , 'users.id' , '=' , 'posts.user_id' ) -> select ([ 'users.*' , 'posts.title' ]) -> get ();
Left Join $users = QueryBuilder :: table ( 'users' ) -> leftJoin ( 'profiles' , 'users.id' , '=' , 'profiles.user_id' ) -> select ([ 'users.*' , 'profiles.bio' ]) -> get ();
Aggregates Count $total = QueryBuilder :: table ( 'users' ) -> count (); $activeUsers = QueryBuilder :: table ( 'users' ) -> where ( 'status' , '=' , 'active' ) -> count ();
Exists $hasUsers = QueryBuilder :: table ( 'users' ) -> where ( 'role' , '=' , 'admin' ) -> exists (); // Returns bool
Insert Queries Inserting Records $success = QueryBuilder :: table ( 'users' ) -> insert ([ 'name' => 'John Doe' , 'email' => 'john@example.com' , 'password' => password_hash ( 'secret' , PASSWORD_DEFAULT ), 'created_at' => date ( 'Y-m-d H:i:s' ), 'updated_at' => date ( 'Y-m-d H:i:s' ) ]); // Returns: true on success, false on failure
The insert() method returns a boolean indicating success. The last insert ID is managed automatically by the underlying PDO connection.
Update Queries Updating Records $affected = QueryBuilder :: table ( 'users' ) -> where ( 'id' , '=' , 1 ) -> update ([ 'name' => 'Jane Doe' , 'updated_at' => date ( 'Y-m-d H:i:s' ) ]); // Returns: number of affected rows
Update with Limit $affected = QueryBuilder :: table ( 'users' ) -> where ( 'status' , '=' , 'pending' ) -> limit ( 10 ) -> update ([ 'status' => 'processed' ]);
Delete Queries Deleting Records $affected = QueryBuilder :: table ( 'users' ) -> where ( 'id' , '=' , 1 ) -> delete (); // Returns: number of affected rows
Delete with Limit $affected = QueryBuilder :: table ( 'logs' ) -> where ( 'level' , '=' , 'debug' ) -> limit ( 100 ) -> delete ();
Be careful when using delete() without a where() clause, as it will delete all records from the table.
Method Chaining All Query Builder methods support fluent chaining:
$users = QueryBuilder :: table ( 'users' ) -> select ([ 'id' , 'name' , 'email' , 'created_at' ]) -> where ( 'status' , '=' , 'active' ) -> whereNotNull ( 'email_verified_at' ) -> orderBy ( 'created_at' , 'DESC' ) -> limit ( 20 ) -> offset ( 0 ) -> get ();
Parameter Binding The Query Builder automatically handles parameter binding to prevent SQL injection:
// Parameters are safely bound $users = QueryBuilder :: table ( 'users' ) -> where ( 'email' , '=' , $userInput ) -> where ( 'status' , '=' , $statusInput ) -> get (); // Even with whereIn $users = QueryBuilder :: table ( 'users' ) -> whereIn ( 'id' , $userProvidedIds ) -> get ();
All user-provided values are automatically escaped and bound as parameters. You never need to manually escape values.
Complete Example Here’s a comprehensive example combining multiple Query Builder features:
use Lyger\Database\ QueryBuilder ; // Complex query with multiple conditions $posts = QueryBuilder :: table ( 'posts' ) -> select ([ 'posts.*' , 'users.name as author_name' ]) -> join ( 'users' , 'posts.user_id' , '=' , 'users.id' ) -> where ( 'posts.status' , '=' , 'published' ) -> whereNotNull ( 'posts.published_at' ) -> whereIn ( 'posts.category_id' , [ 1 , 2 , 3 ]) -> orderBy ( 'posts.published_at' , 'DESC' ) -> limit ( 10 ) -> get (); // Pagination example $result = QueryBuilder :: table ( 'products' ) -> where ( 'stock' , '>' , 0 ) -> where ( 'active' , '=' , true ) -> orderBy ( 'name' , 'ASC' ) -> paginate ( 20 , $_GET [ 'page' ] ?? 1 ); // Insert with error handling try { $success = QueryBuilder :: table ( 'orders' ) -> insert ([ 'user_id' => $userId , 'total' => $total , 'status' => 'pending' , 'created_at' => date ( 'Y-m-d H:i:s' ) ]); if ( $success ) { echo "Order created successfully" ; } } catch ( \ PDOException $e ) { echo "Failed to create order: " . $e -> getMessage (); }
Next Steps
Eloquent Models Learn about the Eloquent ORM layer built on top of Query Builder
Migrations Manage your database schema with migrations