72182

Join tables using model in Yii2

Question:

this is table1

id1 Name ------------ 1 value1 2 value2

this is table2

id2 Name id1 --------------------- 1 value1 2 2 value2 1

this is table3

id3 Name id2 --------------------- 1 value1 2 2 value2 1

this is table4

id4 Name id3 --------------------- 1 value1 2 2 value2 1

I Want to join above 4 tables in <strong>Yii2 with model</strong>

select * from table1 left join table2 on table2.id2 = table1.id1 left join table3 on table2.id3 = table1.id2 left join table4 on table2.id4 = table1.id3

Answer1:

1. Use Yii2 ActiveQuery

<strong>Step-1</strong> : Declaring Relations

To work with relational data using Active Record, you first need to declare relations in Active Record classes. The task is as simple as declaring a relation method for every interested relation, like the following,

class TableOneModel extends ActiveRecord { // ... public function getTableTwo() { return $this->hasMany(TableTwoModel::className(), ['id1' => 'id1']); } } class TableTwoModel extends ActiveRecord { // ... public function getTableThree() { return $this->hasMany(TableThreeModel::className(), ['id2' => 'id2']); } } ..... same create table3 and table4 relation

If a relation is declared with <a href="https://www.yiiframework.com/doc/api/2.0/yii-db-baseactiverecord#hasMany()-detail" rel="nofollow">hasMany()</a>, accessing this relation property will return an array of the related Active Record instances; if a relation is declared with <a href="https://www.yiiframework.com/doc/api/2.0/yii-db-baseactiverecord#hasOne()-detail" rel="nofollow">hasOne()</a>, accessing the relation property will return the related Active Record instance or null if no related data is found.

<strong>Step-2</strong> : Accessing Relational Data

After declaring relations, you can access relational data through relation names. This is just like accessing an object property defined by the relation method. For this reason, we call it relation property. For example,

$query = TableOneModel::find() ->joinWith(['tableTwo.tableThree']) ->all();

Refer <a href="https://www.yiiframework.com/doc/api/2.0/yii-db-activequery" rel="nofollow">yii\db\ActiveQuery</a>.

2. Use Yii2 DB Query

$query = (new \yii\db\Query()) ->from('table1 as tb1') ->leftJoin('table2 as tb2', 'tb1.id1 = tb2.id1') ->leftJoin('table3 as tb3', 'tb2.id2 = tb3.id2') ->leftJoin('table4 as tb4', 'tb3.id3 = tb4.id3') ->all();

Refer <a href="https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder" rel="nofollow">Query Builder documentation</a> and <a href="https://www.yiiframework.com/doc/api/2.0/yii-db-query#leftJoin()-detail" rel="nofollow">leftJoin()</a>.

Answer2:

Use Gii to generate models, and if the foreign keys are defined well in the database, then the relations will be generated in your models. If not, then you can define the relations yourself. <a href="https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#relational-data" rel="nofollow">See it here how to define those relations in a Yii2 model.</a><br /> Then you should be able to access an attribute of the model Table4 by doing this:

$table1 = Table1::findById(1); var_dump($table1->table2->table3->table4->attributes);

Recommend

  • When and how is the Java DB updated and synced with Apache Derby?
  • How to get not only single record but all records that belong to specific search query
  • Strange multiple assignment error in C++
  • Is it good to have multiple database running in a same project?
  • Sharing Redis settings across routes
  • Batching in py2neo
  • Why does NotifyIcon not set SynchronizationContext?
  • How do we set maximum_bad_records when loading a Bigquery table from dataflow?
  • Group variable in cobol
  • How to initialize context? [closed]
  • Checking if the Faye server exists before running it for my Rails app
  • CSS how to fix an element to scroll horizontally with the page but not vertically?
  • how to change api_token column in token guard
  • Can my PDF ping my server when it is opened?
  • How to retrieve information from antrun back to maven?
  • Why use database factory in asp.net mvc?
  • Can someone please explain to me in the most layman terms how to use EventArgs?
  • converting text file into xml using php?
  • Unable to install Git-core+svn by MacPorts
  • Unable to get column index with table.getColumn method using custom table Model
  • Django simple Captcha “No module named fields” error
  • custom UITableViewCell with image for highlighting
  • Get history of file changes from TFS to implement custom “blame”-behaviour of exceptions
  • Visual Studio 2010 debugger build correctly - compiler pdb and linker pdb not in synch?
  • Custom Tabgroup Appcelerator
  • Could not find rake using whenever rails
  • Where to put my custom functions in Wordpress?
  • Trying to switch camera back to front but getting exception
  • Statically linking a C++ library to a C# process using CLI or any other way
  • python regex in pyparsing
  • Free memory of cv::Mat loaded using FileStorage API
  • Angular 2 constructor injection vs direct access
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • embed rChart in Markdown
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • How does Linux kernel interrupt the application?
  • Programmatically clearing map cache
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • How can I use `wmic` in a Windows PE script?
  • Unable to use reactive element in my shiny app