Search code examples
phpjoinyii2yii2-model

Join multiple tables in Yii2 framework


I have 4 tables in my DB:

Table project_ppi:

  • id (PRIMARY KEY)

Table scopus_author:

  • id (PRIMARY KEY),
  • project_ppi, -> linked to id in table project_ppi
  • author_scopus_id

Table author_subject_area:

  • author_id (PRIMARY KEY) -> linked to id in table scopus_author

Table project_author_match:

  • project_ppi (PRIMARY_KEY),
  • author_scopus_id (PRIMARY KEY),
  • match_value

What I need to do:

In practice, given a project (table project_ppi) I need to show all the authors (table scopus_author) linked to this project and for each author show his/her area of working (table author_subject_area) but order the result by match_value (table project_author_match).

I'm able to do it in "normal SQL" statements but I'm stuck in doing it in Yii2 framework. I have a model for each of these tables in my php but I don't really know where to start.
Can anyone give me a hint?

Thanks in advance!

Solution

  • In short

    There are already a handful of guides that can help you, and stating that you use a normal SQL database; like MariaDB or MySQL therefore you ought to use ActiveRecord Models. More of details here (https://www.yiiframework.com/doc/guide/2.0/en/db-active-record)

    A bit of details

    As you should do, you have models for each table, then in each model you add a method that is preferable to be called "getSomething" and this uses the "hasOne" or "hasMany" relation between tables, then in your Yii query you just use the joinWith('relationName') method will making your query. An example:

    $query = Post::find()->joinWith('user')->where(['user.id' => 1]);

    And this query will get all posts that were written by user whose ID is 1. Assuming a simple database with 2 tables user, post