How to implement CRUD with many:many relationship

Question

im new to atk4, but i couldn't find some simple examples for using CRUD with many:many relationships between tables.

I read in some instructions that M:M is best implemented with intermediate table, wich seems logical..

For example

Model ONE has:
$this->hasMany('Table1Table2','table1_id');

Model TWO has:
$this->hasMany('Table1Table2','table2_id');

And Intermediate Model (Table1Table2) has:
$this->hasOne('Table1');
$this->hasOne('Table2');

Wich generates this table, wich is OK:

create table table1table2 (  
  id int auto_increment not null primary key,
  table1_id varchar(255),
  table2_id varchar(255));

But how to implement CRUD? - how to implement listings, adding new, edit etc..

When on page i simply insert like this:

$this->add('CRUD')->setModel('Table1');

There is no relationship generated... It would be nice that user could select (on add and edit of table1) values from table2.

From reading and wathing tutorials i have idea, but maybe is totally overwork, so im really asking what is the best way at ATK4 for this problem?

My idea:

• add multiple dropdown to edit and add form and populate it with table2 values. If in edit, check in intermediate table for what is already checked.
• extend basic CRUD, on formSubmitSuccess insert selected values from dropdown to intermediate table
• do it differently for edit/add new..
• later, do similar check for GRID&other stuff... can generate a lot of extra work..

So, i see its doable, but i'm shure M:M relationships are very common (it's basically two 1:M), so maybe is there much better solution?

Thank you in advance.

Answer 1

No doubts you'll need intermediate table in relational DB design.

Question is, how you define Models. One solution is, as you already explained yourself, with 3 models. But you can also do something similar with just two models and using joins in your model definitions to join them directly to intermediate DB table. Idea here is - Model is not the same as DB table in general. Model is something more than DB table. One model can join up like 10 tables and do something fancy with them :)

I guess there is no nice out-of-the-box solution for such interface (View) which will fit all needs. But in some cases, if you only need to set links (yes/no) between two tables, you can use form + grid + grid->addSelectable() or form + crud + crud->grid->addSelectable(). With such construct you can, for example, associate multiple user roles to users or Apps to Admins or favorite colors to people etc.

If you have more data fields in intermediate table than just linking id's, then you'll have to come up with some custom code. But I guess you can still take a peek on grid->addSelectable() method to grab some idea.

P.S. Sorry, this time I have no ready-to-use example :)

Answer 2

You need to create proxy table and create one to many relations between three tables

class Model_Admin extends Model_Table {
    function init() {
        parent::init();
        $this->haveMany('AdminApp');
    }
}

class Model_App extends Model_Table {
    function init() {
        parent::init();
        $this->haveMany('AdminApp');
    }
}

// this is proxy table
class Model_AdminApp extends Model_Table {
    function init() {
        parent::init();
        $this->haveOne('Admin');
        $this->haveOne('App');
    }
}

actually there is no other way to create many-to-many relations