Laravel3 Study Notes (5)

Original Author's blog: ieqi.net

========================================================== ========================================================== ================================

Model

In the MVC Web framework, the model exists as the data abstraction. In the model layer, we place various data processing and operations directly, then, we expose abstract data operations to the model class to the Controller. In this way, in the controller, we do not have to stick to the details of the specific data reality, such as how the database is connected, data Types, packaging of access methods, and so on. Developers only need to focus on the business logic to perform data operations.

 

The Design of laravel3's model system provides us with great convenience. To put it simply, the data architecture of laravel3 is divided into four parts:

First, it is directly related to the database connection. This part of the user is represented by the database connection configuration and data connection designation.
Second, the query builder named fluent maps various methods in the called DB into database SQL statements for database operations.
Third, the ORM tool named eloquent provides users with object-oriented data encapsulation, that is, the model class in laravel3.
4. Support tools related to data operations, such as migrations that facilitate database deployment and destruction, and schema Builder Tools for database mode management.

Let's explain separately:

Laravel3 and database

The laravel3 framework supports the following types of databases by default:

-MySQL
-PostgreSQL
-SQLite
-SQL Server

Of course, you must have PHP module support for the underlying database to be used in the framework. The following table lists how to install the PHP database support modules.

The database connection configuration is written in the application/config/database. php file.

Each type of database has its own configuration items, which can be configured more easily. Note that for the configuration of the default field, the database connection configured for the default field is used as the default database connection, that is, when you call the DB class, the database connection operated when connect is not specified.

Original SQL query and fluent Query Builder

After the library connection is established, we can directly operate the database through various methods in the DB class. However, it should be noted that the direct use of query statements or methods for database operations increases the Coupling Degree of the program itself, making the code more difficult to maintain, we often say that data ing is processing objects. Of course, this cannot solve all the problems. In some cases, we still need to use basic queries for operations.

In laravel3, it is easy to use SQL to directly operate the database, that is, use the DB: Query Method to pass the SQL statement as a parameter:

// Query information of all users tables $ users = DB: Query ('select * From users ');

To bind a parameter to a query, do you need to use it in an SQL statement? Placeholder number, and the parameter group is an array in order, passed as the second parameter of the query:

// Bind a parameter $ users = DB: Query ('select * from users where name =? ', Array ('test'); // bind an array $ success = DB: Query ('insert into users values (?, ?) ', $ Bindings );

Laravel3 also provides some syntactic sugar that makes query simpler:

// Obtain only the content of the first row of the query result $ user = DB: First ('select * from users where id = 1 '); // obtain only the content of a column in the query result $ email = DB: only ('select email from users where id = 1 ');

To operate other database connections:

// Obtain the database connection named otherdb $ otherdb = DB: Connection ('otherdb ')

Next, let's talk about fluent. Fluent is the Query Builder in laravel3. Simply put, it frees developers from directly facing complicated SQL statements, instead, the database is operated in a more unified process and object-oriented mode.

First, we can obtain the object of a table, and then perform various data operations on this object:

// Get the operation object of the users table $ query = DB: Table ('users ');

Get Data

Obtain all data in the table and return an array:

// Obtain all data in the users table $ users = DB: Table ('users')-> get ();

Obtain the first data in the table:

// Obtain the first day data in the users table $ user = DB: Table ('users')-> first ();

Obtain data with the primary key $ ID:

// Obtain the data whose ID is $ ID in the users table $ user = DB: Table ('users')-> Find ($ id );

Query conditions and retrieval conditions:

// Obtain the email field $ email = DB: Table ('users')-> where ('id', '=', 1) for the ID 1 record in the users table) -> only ('email '); // obtain the ID and email field $ user = DB: Table ('users')-> get (Array ('id ', 'email as user_email '); // obtain non-duplicate data in the users table $ user = DB: Table ('users')-> distinct ()-> get ();

Query Conditions

Multi-condition and parallel relationship:

return DB::table(‘users‘)    ->where(‘id‘, ‘=‘, 1)    ->where(‘email‘, ‘=‘, ‘[email protected]‘)    ->first();

Or link:

return DB::table(‘users‘)    ->where(‘id‘, ‘=‘, 1)    ->or_where(‘email‘, ‘=‘, ‘[email protected]‘)    ->first();

Similar to the where method, there are four methods: where_in, where_not_in, where_null, and where_not_null, which correspond to the in, not in, is null, is not null conditions in SQL:

DB::table(‘users‘)->where_in(‘id‘, array(1, 2, 3))->get();DB::table(‘users‘)->where_not_in(‘id‘, array(1, 2, 3))->get();DB::table(‘users‘)    ->where(‘email‘, ‘=‘, ‘[email protected]‘)    ->or_where_in(‘id‘, array(1, 2, 3))    ->get();DB::table(‘users‘)    ->where(‘email‘, ‘=‘, ‘[email protected]‘)    ->or_where_not_in(‘id‘, array(1, 2, 3))    ->get();return DB::table(‘users‘)->where_null(‘updated_at‘)->get();return DB::table(‘users‘)->where_not_null(‘updated_at‘)->get();return DB::table(‘users‘)    ->where(‘email‘, ‘=‘, ‘[email protected]‘)    ->or_where_null(‘updated_at‘)    ->get();return DB::table(‘users‘)    ->where(‘email‘, ‘=‘, ‘[email protected]‘)    ->or_where_not_null(‘updated_at‘)    ->get();

Nested conditions can be implemented through closures:

$ Users = DB: Table ('users')-> where ('id', '=', 1)-> or_where (function ($ query) {// The two conditions are of the same level $ query-> where ('age', '>', 25); $ query-> where ('votes ','> ', 100) ;})-> get ();

You can also use dynamic reflection to directly call the writing conditions for methods with field names:

$user = DB::table(‘users‘)->where_email(‘[email protected]‘)->first();$user = DB::table(‘users‘)->where_email_and_password(‘[email protected]ail.com‘, ‘secret‘);$user = DB::table(‘users‘)->where_id_or_name(1, ‘Fred‘);

Join Query

Join is used to query the association between tables:

DB::table(‘users‘)    ->join(‘phone‘, ‘users.id‘, ‘=‘, ‘phone.user_id‘)    ->get(array(‘users.email‘, ‘phone.number‘));

The parameters in the join method are the table name and the on condition.
Left join:

DB::table(‘users‘)    ->left_join(‘phone‘, ‘users.id‘, ‘=‘, ‘phone.user_id‘)    ->get(array(‘users.email‘, ‘phone.number‘));

If there are multiple on conditions, you can also use closures to express them:

DB::table(‘users‘)    ->join(‘phone‘, function($join)    {        $join->on(‘users.id‘, ‘=‘, ‘phone.user_id‘);        $join->or_on(‘users.id‘, ‘=‘, ‘phone.contact_id‘);    })    ->get(array(‘users.email‘, ‘phone.number‘));

Query structure sorting

The order_by method can be used to sort the query results, which is equivalent to the order by statement in the SQL statement:

return DB::table(‘users‘)->order_by(‘email‘, ‘desc‘)->get();

Multi-field sorting:

return DB::table(‘users‘)    ->order_by(‘email‘, ‘desc‘)    ->order_by(‘name‘, ‘asc‘)    ->get();

Limitation and offset

In SQL, we can use limit or top to control the start point and number of retrieved data. In fluent, we can do this:

// Return DB: Table ('users')-> take (10)-> get (); // offset return DB: Table ('users ') -> SKIP (10)-> get ();

Statistical functions

For the built-in functions of Min, Max, AVG, sum, count in SQL, we can use the following functions in fluent:

$min = DB::table(‘users‘)->min(‘age‘);$max = DB::table(‘users‘)->max(‘weight‘);$avg = DB::table(‘users‘)->avg(‘salary‘);$sum = DB::table(‘users‘)->sum(‘votes‘);$count = DB::table(‘users‘)->count();

Of course, the following conditions can be used together:

$count = DB::table(‘users‘)->where(‘id‘, ‘>‘, 10)->count();

Insert data

Using FLUENT to insert data, we can directly organize the inserted data into an array. Note that the key value name corresponds to the field name of the data table:

DB::table(‘users‘)->insert(array(‘email‘ => ‘[email protected]‘));

After we insert a record, we will perform the following operations. In this case, we need the ID of the inserted data (of course, the table structure with the auto-incrementing primary key named ID is required ), laravel3 provides us with such syntactic sugar, which is very convenient:

$ Id = DB: Table ('users')-> insert_get_id (Array ('email '=>' [email protected] '); // you can use $ id for other processing.

Update Data

Use the update method to update data and input the array corresponding to the field name:

$affected = DB::table(‘users‘)->update(array(‘email‘ => ‘[email protected]‘));

Combined with query conditions:

$affected = DB::table(‘users‘)    ->where(‘id‘, ‘=‘, 1)    ->update(array(‘email‘ => ‘[email protected]‘));

Delete data

Delete data. The delete method can be used with the following conditions:

$affected = DB::table(‘users‘)->where(‘id‘, ‘=‘, 1)->delete();

You can directly delete data using the primary key ID:

$affected = DB::table(‘users‘)->delete($id);

Other methods

If you need to use some fluent features in fluent, such as user-defined functions in the database, you can use the raw method:

DB::table(‘users‘)->update(array(‘updated_at‘ => DB::raw(‘NOW()‘)));DB::table(‘users‘)->update(array(‘votes‘ => DB::raw(‘votes + 1‘)));

For the preceding votes + 1 Operations, fluent also provides a simpler method:

// Increment DB: Table ('users')-> increment ('votes '); // decrease DB: Table ('users ') -> decrement ('votes ');