thinkphp Query (multi-table query)

thinkphp Query (multi-table query) can use the table () method or the Join method, see the example:

1, table method: Define the name of the data table to manipulate, you can dynamically change the current operation of the data table name, need to write the full name of the data table, including the prefix, you can use aliases, for example:

$Model->table (' Think_user user ')

->where (' status>1 ')

->select ();

$Model->table (' think_blog blog,think_type type ')
->where (' blog.typeid=type.id ')
->field (' blog.id as id,blog.title,blog.content,type.typename as type ')
->order (' blog.id desc ')
->limit (5)
->select ();

The parameters of the table method support the use of strings and arrays, array mode:

$Model->table (Array (' Think_user ' + ' user ', ' think_group ' = ' group ')

->where (' status>1 ')

->select ();

The advantage of using an array approach is to avoid situations where errors occur because of table name and keyword collisions. Note: If you do not define a table method, the data table corresponding to or defined by the current model is automatically obtained by default. 2. Join method: Query join support, the parameters of the Join method support strings and arrays, and the Join method is the only method that can be called multiple times in a coherent operation. For example:

$Model->join (' work on artist.id = Work.artist_id ')

->join (' card on artist.card_id = Card.id ')

->select ();

Left Join
$Model->table (' User U ')

->join (' News N on U.id=n.cid ')

->field (' u.*,n.* ')

->order (' id desc ')

->limit (' 8 ')

->findall ();

By default, the left join method, if you need to use a different join method, you can change to

$Model->join (' Right join work on artist.id = Work.artist_id ')

->select ();

Right Join
$Model->table (' User U ')
->join (Array (' Right ', ' news N on U.id=n.cid '))
->field (' u.*,n.* ')
->order (' id desc ')
->limit (' 8 ')
->findall ();

The Join method can only be used once if the parameters of the join method are in an array, and cannot be mixed with the string method.

$Model->join (Array (' work on artist.id = work.artist_id ', ' card on artist.card_id = Card.id '))

->select ()

Using this coherent operation method can effectively improve the code definition and development efficiency of data query. To view the SQL statements for a coherent operation:

echo $Model->getlastsql (); Print the SQL statement and look at

Example 2:
1. Table ()

$list = $user->table (' User_status stats, user_profile profile ')->where (' stats.id = Profile.typeid ')->field (' Stats.id as ID, stats.display as display, profile.title as title,profile.content as content ')->order (' stats.id desc ')- >select ();

2.1. Join () 2 Table query

$user = new Model (' user ');

$list = $user->join (' Right join user_profile on user_stats.id = User_profile.typeid ');

2.2. Join () Multi-table query

$list = $Form->join (' think_sort on think_form.sort_id = think_sort.sort_id ')->join (' Think_brand on THINK_ form.brand_id = think_brand.brand_id ')->select ();

3. Native Query

$Model = new Model ();

$sql = ' Select A.id,a.title,b.content from Think_test1 as a, think_test2 as B where a.id=b.id '. $map. ' ORDER by a.ID '. $so Rt. ' Limit '. $p->firstrow ', '. $p->listrows;
$voList = $Model->query ($sql);

thinkphp Query (multi-table query)