Eloquent
Introducation
Vania includes Eloquent, an object-relational mapper (ORM) that makes it enjoyable to interact with your database. When using Eloquent, each database table has a corresponding "Model" that is used to interact with that table. In addition to retrieving records from the database table, Eloquent models allow you to insert, update, and delete records from the table as well.
Generating Model Classes
To get started, let's create an Eloquent model. Models typically live in the app\models directory and extend the Vania Model class. You may use the make:model Vania command to generate a new model:
vania make:model model_name
Eloquent Model Conventions
Models generated by the make:model command will be placed in the app/Models directory. Let's examine a basic model class and discuss some of Eloquent's key conventions:
import 'package:vania/vania.dart';
class User extends Model {
}
Table Names
In the example above, you may have noticed that we didn't explicitly specify the database table corresponding to our User model. By default, when you create the model using the Vania CLI, it will prompt you to enter the table name. However, if you wish to customize the table name further, you can use the super keyword to override it:
import 'package:vania/vania.dart';
class User extends Model {
User() {
super.table('users');
}
}
// Model with the new table name
class User extends Model {
User() {
super.table('user_tables');
}
}
By following these conventions, you can effortlessly define and interact with database tables using Eloquent within your Vania application.
Retrieving Results
Retrieving All Rows From A Table
To begin a fluent query, use the query method on the Vania model. The query method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally get the results. In this example, let's just get all records from a table:
import 'package:blog/app/models/user.dart';
import 'package:vania/vania.dart';
class HomeController extends Controller {
Future<Response> index(Request request) async {
final users = await User().query().get();
return Response.json(
{
'message': 'Hi ${users[0]['first_name']} ${users[0]['last_name']}',
}
);
}
}
Retrieving A Single Row / Column From A Table
If you just need to retrieve a single row from the database table, you may use the first method. This method will return a single Map
import 'package:blog/app/models/user.dart';
import 'package:vania/vania.dart';
class HomeController extends Controller {
Future<Response> index(Request request) async {
final user = await User().query().where('id','=',1).first();
return Response.json(
{
'message': 'Hi ${user['first_name']} ${user['last_name']}',
}
);
}
}
Retrieving exists
Determine if any rows exist for the current query.
final user = await User().query().where('id','=',1).exists();
Retrieving A List Of Column Values
If you would like to retrieve an array containing the values of a single column, you may use the pluck method. In this example, we'll retrieve an array of role titles:
final users = await User().query()->pluck('first_name')
for(String user in users){
print(user);
}
You may also specify a custom key column for the returned array:
final users = await User().query()->pluck('first_name','name')
Pagination And SimplePagination
paginate
The paginate method is used to fetch a paginated set of data. It allows you to specify the number of items per page and the page number. This method returns a detailed pagination object which includes metadata such as total items, total pages, current page, and the data items.
Syntax
Future<PaginatedResult> paginate([int perPage = 15, int page = 1])
- perPage (optional): The number of items to be displayed per page. Defaults to 15.
- page (optional): The page number to retrieve. Defaults to 1.
Example Usage
final user = await User().query().paginate();
final user2 = await User().query().where('name','like','%Vania%').paginate(10, 2);
simplePagination
The simplePagination method is used to fetch a paginated set of data in a simplified format. It also allows you to specify the number of items per page and the page number. This method returns a simpler pagination object which includes only the data items and basic pagination information.
SimplePagination Syntax
Future<SimplePaginatedResult> simplePagination([int perPage = 15, int page = 1])
- perPage (optional): The number of items to be displayed per page. Defaults to 15.
- page (optional): The page number to retrieve. Defaults to 1.
Example Usage simplePagination
final user = await User().query().where('name','like','%Vania%').simplePagination();
final user2 = await User().query().simplePagination(10, 2);
Selects
Specifying A Select Clause
Of course, 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
final users = await User().query().select(['first_name','last_name','email']).get();
The distinct method allows you to force the query to return distinct results:
final users = await User().query().distinct().get();
Raw Expressions
Sometimes you may need to use a raw expression in a query. These expressions will be injected into the query as strings, so be careful not to create any SQL injection points! To create a raw expression
final users = await User().query().selectRaw('count(*) as user_count, status').get();
Joins
Inner Join Statement
To perform a basic SQL "inner join", you may use the join method on a query. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. Of course, as you can see, you can join to multiple tables in a single query:
final user = await User().query()
.join('clientes_grupos','clientes_grupos.user_id','=','users.id')
.first();
Left Join Statement
If you would like to perform a "left join" instead of an "inner join", use the leftJoin method. The leftJoin method has the same signature as the join method:
final user = await User().query()
.leftJoin('clientes_grupos','clientes_grupos.user_id','=','users.id')
.first();
Right Join Statement
If you would like to perform a "right join" instead of an "inner join", use the rightJoin method. The rightJoin method has the same signature as the join method:
final user = await User().query()
.rightJoin('clientes_grupos','clientes_grupos.user_id','=','users.id')
.first();
Unions
The query builder also provides a quick way to "union" two queries together. For example, you may create an initial query, and then use the union method to union it with a second query
final unionUsers = User().query().whereNull('first_name');
final users = await User().query()
.union(unionUsers)
.get();
Where Clauses
Simple Where Clauses
To add where clauses to the query, use the where method on a query builder instance. The most basic call to where 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 evaluate against the column.
For example, here is a query that verifies the value of the "votes" column is equal to 100:
final votes = await User().query().where('votes', '=', 100).get();
Of course, you may use a variety of other operators when writing a where clause:
final votes = await User().query().where('votes', '=>', 100).get();
final votes = await User().query().where('votes', '<>', 100).get();
final votes = await User().query().where('votes', 'like', '100%').get();
Or Statements
final votes = await User().query()
.where('votes', '=', 100)
.orWhere('name','=','John')
.get();
Additional Where Clauses
whereBetween / whereNotBetween
The whereBetween method verifies that a column's value is between two values:
final voutes = await User().query()
.whereBetween('votes', [1, 100])
.get();
The whereNotBetween method verifies that a column's value lies outside of two values:
final voutes = await User().query()
.whereNotBetween('votes', [1, 100])
.get();
whereIn / whereNotIn
The whereIn method verifies that a given column's value is contained within the given list:
final users = await User().query()
.whereIn('id', [1, 2, 3])
.get();
The whereNotIn method verifies that the given column's value is not contained in the given list:
final users = await User().query()
.whereNotIn('id', [1, 2, 3])
.get();
whereNull / whereNotNull
The whereNull method verifies that the value of the given column is NULL:
final users = await User().query()
.whereNull('updated_at')
.get();
The whereNotNull method verifies that the column's value is not NULL:
final users = await User().query()
.whereNotNull('updated_at')
.get();
whereDate / whereMonth / whereDay / whereYear
The whereDate method may be used to compare a column's value against a date:
final users = await User().query()
.whereDate('created_at', '=','2023-12-25')
.get();
The whereMonth method may be used to compare a column's value against a specific month:
final users = await User().query()
.whereMonth('created_at', '=','25')
.get();
The whereDay method may be used to compare a column's value against a specific day of the month:
final users = await User().query()
.whereDay('created_at', '=','25')
.get();
The whereYear method may be used to compare a column's value against a specific year:
final users = await User().query()
.whereYear('created_at', '=','2023')
.get();
Advanced Where Clauses
Parameter Grouping
Sometimes you may need to create more advanced where clauses such as "where exists" or nested parameter groupings. The Laravel query builder can handle these as well. To get started, let's look at an example of grouping constraints within parenthesis:
final users = User().query()
.where('name','=', 'John')
.orWhere((QueryBuilder q){
q.where('votes', '>', 100)
.where('title', '<>', 'Admin');
})
.get();
As you can see, passing a Closure into the orWhere method instructs the query builder to begin a constraint group. The Closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group. The example above will produce the following SQL:
select * from users where name = 'John' or (votes > 100 and title <> 'Admin')
The whereExists method allows you to write where exists SQL clauses. The whereExists method accepts a Closure argument, which will receive a query builder instance allowing you to define the query that should be placed inside of the "exists" clause:
final users = User().query()
.whereExists((QueryBuilder q){
q.select(['id'])
.from('orders')
.whereRaw('orders.user_id = users.id');
})
.get();
The query above will produce the following SQL:
select * from users
where exists (
select 1 from orders where orders.user_id = users.id
)
Ordering, Grouping, Limit, & Offset
orderBy
The orderBy method allows you to sort the result of the query by a given column. The first argument to the orderBy method should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either asc or desc:
final users = await User().query().orderBy('name', 'desc').get();
groupBy / having
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:
final users = await User().query().groupBy('account_id').having('account_id', '>', 100)
.get();
skip / take
To limit the number of results returned from the query, or to skip a given number of results in the query (OFFSET), you may use the skip and take methods:
final users = await User().query().skip(10).take(5).get();
Alternatively, you may use the limit and offset methods. These methods are functionally equivalent to the take and skip methods, respectively:
final users = await User().query().offset(10).limit(5).get();
Create and Return Inserted Data
The create method not only inserts a new record into the database but also returns the inserted data. This is particularly useful when you need to access the newly created record immediately after insertion.
final user = await User().query().create({"email":'[email protected]', "name": "Vania"});
In this example, a new user is created with the specified mobile number and name. The create method returns the inserted user data, allowing you to work with it directly after the insertion.
Inserts
The Model also provides an insert method for inserting records into the database table. The insert method accepts a Map of column names and values to insert:
await User().query().insert({
'name': 'John',
'email': '[email protected]',
'password': '123456',
});
Auto-Incrementing IDs
If the table has an auto-incrementing id, use the insertGetId method to insert a record and then retrieve the ID
final userId =await User().query().insertGetId({
'name': 'John',
'email': '[email protected]',
'password': '123456',
});
Updates
Of course, in addition to inserting records into the database, the Model can also update existing records using the update method. The update method, like the insert method, accepts a Map of column and value pairs containing the columns to be updated. You may constrain the update query using where clauses:
await User().query()
.where('id','=',1)
.update({
'name': 'Vania',
});
Deletes
Of course, the Model may also be used to delete records from the table via the delete method:
await User().query()
.where('id','=',1)
.delete();
DB Transaction
The DB Transaction feature in Vania allows you to execute a series of database operations within a single transaction. This ensures that all operations are completed successfully before committing the changes, or rolled back in case of an error, maintaining the integrity of your database.
dbTransaction((Connection con) async {
await User().query().insert({"email":'[email protected]', "name": "Vania"});
await User().query().insert({"email":'[email protected]', "name": "Dev Vania"});
});
In the example above, multiple insert operations are executed within a transaction. If any of the inserts fail, all changes will be rolled back, ensuring data consistency.
Special thanks to Isaque Neves for the helpful Dart Eloquent package.