Laravel Eloquent Use small note

Original address: http://blog.onlywan.cc/14843810761202.html Laravel Eloquent use of small notes

Today, because of the need to develop a database business middle tier, we started to study laravel eloquent, because when we first started using the Laravel framework, we used Query Builder to write SQL similar to

Db::connection (' MyDB ')->table (' Mylove ')
                        ->where (' name ', ' Guowan ')
                        ->get ();

A bit more complicated SQL use Db::raw

Db::connection (' MyDB ')->table (' Mylove ')->select (Db::raw (' Count (') ' as Mylovecount '))
                        ->where (' Name ', ' Guowan ')
                        ->get ();

In the work of learning attitude began to study eloquent, laravel Chinese documents, began to design eloquent Model. Here is the table about the field (due to compatibility with the old system requirements, table field design and the current business does not match, here is not with the discussion ~) table Structure

CREATE TABLE ' user_ext ' (
  ' user_id '     int () not             null,
  ' realname '    varchar (255)        DEFAULT NULL,
  ' gender '      int (one) not             null default ' 0 ',
  ' birthday '    datetime            default NULL,
  ' Comefrom '    varchar (255)        default NULL,
  ' QQ '          varchar (255)        default NULL,
  ' Weibo '       varchar ( 255)        default NULL,
  ' blog '        varchar (255)        default NULL,
  ' mobile '      varchar (255)        Default NULL,
  PRIMARY KEY (' user_id ')
) engine=innodb default Charset=utf8



CREATE TABLE ' user ' (
  ' user_id '     Int (ten) not NULL auto_increment,
  ' username '    varchar (+)    DEFAULT NULL,
  ' email '       varchar (255)    Default NULL,
  ' user_img '    varchar (255)    default NULL,
engine=innodb default Charset=utf8

Create Eloqueue ModelUser

<?php

namespace App\http\models\eloquent;

Use Illuminate\database\eloquent\model;

Class Cuser extends model
{
    /**
     * Data table associated with the models.
     *
     * @var String */
    protected $table = ' user ';

    /*
     * database table PRIMARY KEY
     *
     * @var String */
    protected $primaryKey = ' user_id ';

    /*
     * Cancel automatic maintenance create_at,update_at field
     * * @var String */public
    $timestamps = false;


    /
     * Gets the extended information record associated with the specified user */public
    function Hasoneext ()
    {
        return $this->hasone (' App\http \models\eloquent\cuserext ', ' user_id ', ' user_id ');
    }
}

User_ext

<?php

namespace App\http\models\eloquent;

Use Illuminate\database\eloquent\model;

Class Cuserext extends model
{

    /**
     * Data table associated with the models.
     *
     * @var String */
    protected $table = ' ac_user_ext ';

    /*
     * database table PRIMARY KEY
     *
     * @var String */
    protected $primaryKey = ' user_id ';

    /*
     * Cancel automatic maintenance create_at,update_at field
     * * @var String */public
    $timestamps = false;


    Public Function Acuser ()
    {
        return $this->belongsto (' App\http\models\eloquent\cuser ');
    }
}

The user and the User_ext table are 1-to-1 relationships. Note

The Hasoneext method in the user model uses the Hasoneext method name, which is named by the method and can be used to know the relationship to the Userext table when invoking the method.

The Hasone function, the first parameter is the CLASSPATH, the second parameter foreign key, which is the primary key of the Userext table, and the third parameter is the user table primary key. When you use it, the second and third parameters are not specified, and an error occurs.

The following is the main content of today's record, in the use of the process, there are some problems, and the corresponding solution to the problem, there may be some problems have not been resolved or solved the bad, here record, add the impression, you can discuss with other classmates 1. Dependency Method Hasoneext

Call the following method

$oUser = Cuser::find ($sUMId)->hasoneext ();

The result returns the data in the Userext table. My intention was to do the corresponding correlation query, find out two tables of data. Then on the Internet various search eloquent two tables, return two table fields.

The final solution is as follows:

$oUser = Cacuser::with (' Hasoneext ')->find ($sUMId);

Query Result:

Array
(
    [user_id] + 1
    [username] = admin
    [email] = wanguowan521@163.com
    [user_img] = > 201303/26132122j2lg.jpg
    [has_one_ext] = Array
        (
            [user_id] = 1
            [realname] =
            [ Gender] + 1
            [Birthday] = [
            comefrom] + * *, not limited to
            [QQ] =
            [Weibo] [
            blog] = =
            [Mobile] =
        )

)

This relies on table data to use the method name as a key as part of the return result, which needs to be translated for the business interface when a one-dimensional array is required. Fortunately for the business layer, want to block the underlying data layer field details, originally need to do a translation, so here is not a big problem.

Here with syntax, is the so-called pre-load syntax in eloquent, mainly to solve the ORM (Object Relation Mapping) n+1 Query Problem – detailed description. In the online query process, although here is 1 to 1 relationship, but if this solution, will be a join query, into two queries, for the future high concurrency scenario, a little unacceptable. But no workaround was found.

Helpless, try to print eloquent executed SQL, view specific SQL statements (print Laravel Execute SQL method is more, can reference), the code is as follows:

Db::enablequerylog ();

$oUser = Cuser::with (' Hasoneext ')->find ($sUMId);

Print_r (
    db::getquerylog ()
);

The printing results are as follows:

Array
(
    [0] = = Array
        (
            [query] = select * from ' user ' where ' user '. ' user_id ' = "Limit 1
            [bindi NGS] = array
                (
                    [0] = 1
                )

            [TIME] = 0.56
        )

    [1] = = Array
        (
            [query] = SELECT * from ' User_ext ' where ' user_ext '. ' user_id ' in (?)
            [bindings] = = Array
                (
                    [0] = 1
                ) [time

            ] = 0.32
        )

)

As can be seen, SQL first query to the main data according to USER_ID, and then in the go to rely on the table to do in query, it does solve the ORM n+1 query problem, but for the direct use of SQL, or one more query.

Here to find a more interesting thing, log has a time value, is this is the SQL execution times, if this is the execution time, that can simply verify the SQL execution efficiency problem, and then began to query the data, finally found in the source of the answer, the source code is as follows: detailed links

    /** * Run A SQL statement and log its execution context.
     * * @param string $query * @param array $bindings * @param Closure $callback * @return Mixed
        * * @throws queryexception */protected function run ($query, $bindings, Closure $callback) {
        $start = Microtime (true); To execute the statement, we'll simply call the callback, which'll actually//run the SQL against the PDO Co Nnection.
        Then we can calculate the time it//took to execute and log the query SQL, bindings and time in our memory.
        try {$result = $callback ($this, $query, $bindings); }//If An exception occurs when attempting to run a query, we'll format the error/message to include T He bindings with SQL, which'll make this exception A//IoT more helpful to the developer instead of just the DA
        Tabase ' s errors.
          catch (\exception $e) {  throw new Queryexception ($query, $bindings, $e); }//Once We have run the query we'll calculate the time that it took to run and//then log the query, Bindings, and execution time so we'll report them on//the event, the developer needs them.
        We ll log time in milliseconds.
        $time = $this->getelapsedtime ($start);
        $this->logquery ($query, $bindings, $time);
    return $result;
     }/** * Get the elapsed time since a given starting point. * * @param int $start * @return Float */protected function GetElapsedTime ($start) {RE
    Turn round ((Microtime (True)-$start) * 1000, 2); }

Here you can see that time is the SQL execution times, and the units are milliseconds.

Here you can test the single join and use the eloquent with query efficiency comparison, the code is as follows:

Db::enablequerylog ();

Db::table ('  user ')
    ->leftjoin (' User_ext as ext ', ' user.user_id ', ' = ', ' ext.user_id ')
    ->where (' user.user_id ', 1)
    ->get ();

$oUser = Cuser::with (' Hasoneext ')->find ($sUMId);

Print_r (
    db::getquerylog ()
);

The results are as follows:

array ([0] = = Array ([query] = select * from ' user ' as ' user ' Le
            FT join ' user_ext ' as ' ext ' on ' user '. ' user_id ' = ' ext '. ' user_id ' where ' user '. ' user_id ' =?
        [bindings] = = Array ([0] = 1) [Time] = 0.65 
            ) [1] = = Array ([query] = select * from ' user ' where ' user '. ' user_id ' =? Limit 1 [bindings] = = Array ([0] = 1) [Time ] = 0.35) [2] = = Array ([query] = = select * from ' User_ext ' where ' user_ext '.
            ' user_id ' in (?)
        [bindings] = = Array ([0] = 1) [TIME] = 0.35 )

)

From the results can be seen, the implementation of a time compared to the execution of two times, the gap is not very large, but objectively speaking, this does not explain what the problem; First, the test is based on the local database, the network impact and latency of a request and two requests is much smaller than the online gap, and secondly, the local test database, two table data The data volume is too small to reflect the efficiency of the real online data query. So here the query results are for reference only, later detailed results, will be in the local forgery of about 100w data volume for testing observation, and consulting company DBA, for the large data volume on the efficiency of the query. Summary

For today's problem solving process, although the feeling did not get the perfect answer, but in the query process also learned a lot of things, here to do a record. For later temperature study.

Here are a few small details to keep in check:

Database query process, in order to save the application server and database server network traffic and database server IO, database query principle is only query return useful fields, for useless large pieces, especially text, etc., do not need, as far as possible, do not query. Database query try not to use Selec *

Eloquent union query Specify field Method 1

$oUser = Cuser::with ([' Hasoneext ' = function ($query) {
        $query->select (' user_id ', ' realname ', ' Gender ', ' BI Rthday ');
        } ])->find ($sUMId, [' user_id ', ' username ', ' email ', ' user_img ']);

of which Query−>select (′userid′,′realname′,′gender′,′bir