How to create a form for many to many relationship

Two models:

ServiceOrders and Services

They have a many-to-many relationship that is captured in a junction table with foreign keys:

Services_to_ServiceOrders

service_id

service_order_id

How can I wire together a form such that for a service order, I can display the multiple services associated with it. I’d like to do something similar to what this link describes: https://mrphp.com.au/blog/advanced-multi-hasmany-model-forms-yii2/ But in this link, it’s a one-to-many relationship.

Relatively new to Yii2, but starting to get a feel for it. What I am struggling with the most is when more than 2 tables are involved in a relation, I have trouble wiring together the GridView search. I can do it fine between two tables. But if there is any form of junction table between two tables, I haven’t been able to figure it out yet.

Seems odd that there isn’t a clear guide on how to do this anywhere as it seems like something that would be very commonly needed.

Any help would be very much appreciated.

Regards,

psr123

No takers? I know this is a fairly general question, and maybe there are some good documentation on how to do this, but I haven’t been able to find it. When I figure it out, I will write a tutorial on it myself. It’s a real pain not to be able to do this.

psr123

Basically, you write a query for the form, complete with joins/filters, etc - and then instruct your form to use that - perhaps with a dataprovider.

The Gii CRUD generator will generate a search action/query/form that you can use as a starting point

Thanks jacmoe,

Could you provide just a little more guidance?

Let me make the question a bit more generic:

Suppose you have the following tables:

services:

mysql> describe services;

+-------------+----------------------+------+-----+---------+----------------+

| Field       | Type                 | Null | Key | Default | Extra          |

+-------------+----------------------+------+-----+---------+----------------+

| id          | smallint(4) unsigned | NO   | PRI | NULL    | auto_increment |

| t_code      | varchar(6)           | NO   |     | NULL    |                |

| description | varchar(240)         | YES  |     | NULL    |                |

+-------------+----------------------+------+-----+---------+----------------+

service_orders:

mysql> describe service_orders;

+-----------------+----------------------+------+-----+---------+----------------+

| Field           | Type                 | Null | Key | Default | Extra          |

+-----------------+----------------------+------+-----+---------+----------------+

| id              | bigint(11) unsigned  | NO   | PRI | NULL    | auto_increment |

| SVONumber       | int(11) unsigned     | NO   |     | NULL    |                |

| date            | timestamp            | YES  |     | NULL    |                |

| driver_id       | int(11) unsigned     | YES  | MUL | NULL    |                |

| battery_type_id | smallint(3) unsigned | YES  | MUL | NULL    |                |

| pay_type_id     | int(4) unsigned      | YES  | MUL | NULL    |                |

| amount          | decimal(10,2)        | YES  |     | NULL    |                |

| t7_destination  | varchar(240)         | YES  |     | NULL    |                |

| status          | varchar(20)          | YES  |     | NULL    |                |

| svo_notes       | varchar(480)         | YES  |     | NULL    |                |

| region_id       | smallint(4) unsigned | YES  | MUL | NULL    |                |

+-----------------+----------------------+------+-----+---------+----------------+

service_orders_to_services (the junction table)

mysql> describe service_orders_to_services;

+------------------+----------------------+------+-----+---------+----------------+

| Field            | Type                 | Null | Key | Default | Extra          |

+------------------+----------------------+------+-----+---------+----------------+

| service_order_id | bigint(11) unsigned  | NO   | MUL | NULL    |                |

| service_id       | smallint(4) unsigned | NO   | MUL | NULL    |                |

| id               | bigint(11) unsigned  | NO   | PRI | NULL    | auto_increment |

+------------------+----------------------+------+-----+---------+----------------+

The relationships are as follows:

One service_order has many service_orders_to_services

and

One service has many service_orders_to_services

So what I would like to do is, in the GRIDVIEW of service_orders_to_services (which was generated by Gii), I want to be able to, for instance, display the driver’s first and last name, which comes from the driver_id field in the service_orders table (see above), which is a foreign key to the employee table’s id field. Here is the employee table:

mysql> describe employees;

+----------------------+-----------------------+------+-----+---------+----------------+

| Field                | Type                  | Null | Key | Default | Extra          |

+----------------------+-----------------------+------+-----+---------+----------------+

| id                   | int(11) unsigned      | NO   | PRI | NULL    | auto_increment |

| hire_date            | timestamp             | YES  |     | NULL    |                |

| first                | varchar(50)           | YES  |     | NULL    |                |

| last                 | varchar(50)           | YES  |     | NULL    |                |

| is_driver            | bit(1)                | YES  |     | NULL    |                |

| phonetic_id          | smallint(4) unsigned  | YES  | MUL | NULL    |                |

| region_id            | smallint(4) unsigned  | YES  | MUL | NULL    |                |

| address              | varchar(100)          | YES  |     | NULL    |                |

| address_line_2       | varchar(100)          | YES  |     | NULL    |                |

| postal               | varchar(6)            | YES  |     | NULL    |                |

| phone                | varchar(16)           | YES  |     | NULL    |                |

| deparment            | varchar(20)           | YES  |     | NULL    |                |

| hourly_rate_base     | decimal(4,2) unsigned | YES  |     | NULL    |                |

| hourly_rate_adjusted | decimal(4,2) unsigned | YES  |     | NULL    |                |

| status               | varchar(20)           | YES  |     | NULL    |                |

| title                | varchar(100)          | YES  |     | NULL    |                |

| writeups             | varchar(5000)         | YES  |     | NULL    |                |

| city                 | varchar(50)           | YES  |     | NULL    |                |

+----------------------+-----------------------+------+-----+---------+----------------+

The issue I am having is that I can easily display fields from either the service_orders table, or the services table, in the junction tables gridview (service_orders_to_services) because these are just one degree away from those tables via the above relations. BUT, I can’t get at any of the fields in the employee table in the service_orders_to_services gridview, because the employees table is one EXTRA degree removed from the junction table. Here is a diagram to help make my problem more clear:

7393

How can I wire together the models so that the employees table’s fields are accessible within the services_to_services_orders table’s models (the junction table’s model), both the base model and the search model.

Many many thanks in advance. I feel like if I can grok this, then I am able to do pretty much everything else I need to do.

:

You can use a database view (s. View (SQL) in Wikipedia, link is not allowed here) that relates the drivers name with the service order table.

The view would be created directly in the database via command line or a DB-tool, or in Yii via a SQL command (CREATE VIEW ).

A view can be used like a table, and can be adressed like one, also in Yii. So Yii would not know that it is not a normal table.

This works as long as you do not ask Yii to write (UPDATE) in it.

Nested relation (a relation of a relation) can easily be handled by ActiveRecord.

For example:

foreach ($serviceOrderToService->services as $service) {

    $driver = $service->driver;

    echo 'The driver is ' . $driver->firstName . ' ' . $driver->lastName;

}

"Active Record - Working with Relational Data"

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#relational-data

Especially the following sub-sectiion:

"Lazy Loading and Eager Loading"

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#lazy-eager-loading

Awesome softark! Much appreciated.

psr123, could you post the code here that worked for you to add the relation to the employee table?

I also appreciate this softark, will use it.