BelongsToMany relationship where both relations are the id in one table

Need Help
1

Dear CakePHP-Experts,

I wanted to create a BelongsToMany relationship where both relations are in one table.

I have a farms table with my farms and a connected_farms table (joining table) which has first_farm_id and second_farm_id related to the farm_id in my farms table.

Now my problem is, that I don’t want double relations like farm1 related to farm2, farm2 related to farm1, how to set the relations in the FarmsTable and ConnectedFarmsTable? At the moment I have FirstFarms and SecondFarms, but with this constellation I have a problem, because it’s possible to create opposite relations as described before.

Hope you understand my problem…

Best regards,

2

Sorry, I’ve tried to help but miss understand you tables.
Show your tables and fields, and show how you see relations.

3

Hey,

first of all thank you.

Here you have a picture of the two tables:

important part of FarmsTable:

public function initialize(array $config)
    {
        parent::initialize($config);

        $this->table('farms');
        $this->displayField('unique_name');
        $this->primaryKey('id');

        $this->addBehavior('Timestamp');

        $this->belongsToMany('FirstFarms', [
            'className' => 'Farms',
            'foreignKey' => 'second_farm_id',
            'targetForeignKey' => 'first_farm_id',
            'joinTable' => 'connected_farms'
        ]);
        $this->belongsToMany('SecondFarms', [
            'className' => 'Farms',
            'foreignKey' => 'first_farm_id',
            'targetForeignKey' => 'second_farm_id',
            'joinTable' => 'connected_farms'
        ]);
    }

Important part of ConnectedFarmsTable:

public function initialize(array $config)
{
parent::initialize($config);

$this->table('connected_farms');
$this->displayField('id');
$this->primaryKey('id');

$this->addBehavior('Timestamp');

$this->belongsTo('FirstFarms', [
    'className' => 'Farms',
    'foreignKey' => 'first_farm_id',
]);
$this->belongsTo('SecondFarms', [
    'className' => 'Farms',
    'foreignKey' => 'second_farm_id',
]);

}

edit-method of FarmsController:

public function edit($id = null) {
        $farm = $this->Farms->get($id, [
            'contain' => ['Bundlers', 'InspectionInstitutions','SecondFarms']
        ]);
        if ($this->request->is(['patch', 'post', 'put'])) {
            $farm = $this->Farms->patchEntity($farm, $this->request->data);
            if ($this->Farms->save($farm)) {
                $this->Flash->success(__('The farm has been saved.'));

                return $this->redirect(['action' => 'index']);
            } else {
                $this->Flash->error(__('The farm could not be saved. Please, try again.'));
            }
        }
        $countries = $this->Farms->Countries->find('list', ['limit' => 200]);
        $users = $this->Farms->Creators->find('list', ['limit' => 200]);
        $secondFarms = $this->Farms->SecondFarms->find('list', ['limit' => 200])->where(array('not' => array('SecondFarms.id' => $id)));
        $bundlers = $this->Farms->Bundlers->find('list', ['limit' => 200]);
        $inspectionInstitutions = $this->Farms->InspectionInstitutions->find('list', ['limit' => 200]);
        $this->set(compact('farm', 'countries', 'users', 'bundlers', 'inspectionInstitutions','secondFarms'));
        $this->set('_serialize', ['farm']);
    }

important part of Farms edit.ctp:

<div class="farms form large-9 medium-8 columns content">
    <?= $this->Form->create($farm) ?>
    <fieldset>
        <legend><?= __('Edit Farm') ?></legend>
        <?php
            echo $this->Form->input('lfbis_number');
            echo $this->Form->input('ggn_number');
            echo $this->Form->input('first_name');
            echo $this->Form->input('last_name');
            echo $this->Form->input('street');
            echo $this->Form->input('zip');
            echo $this->Form->input('city');
            echo $this->Form->input('country_id', ['options' => $countries]);
            echo $this->Form->input('phone');
            echo $this->Form->input('mail');
            
            
            echo $this->Form->input('second_farms._ids', ['options' => $secondFarms]);

/If I use this input, I only get the farms which are connected as second farms, but I also want to show the farms connected as first farms, f.e. if the actual farm is in the second farms ids connected with other first farms - so it’s also possible to connect a first farm A to a second farm B and create the opposite connection (first farm B to second farm A), but this shouldn’t be possible/

            echo $this->Form->input('bundlers._ids', ['options' => $bundlers]);
            echo $this->Form->input('inspection_institutions._ids', ['options' => $inspectionInstitutions]);
        ?>
    </fieldset>
    <?= $this->Form->button(__('Submit')) ?>
    <?= $this->Form->end() ?>
</div>
4

Could we use in table Farm field Referer_id ( it’s your second_farm_id). if you use table connected_farms for creation Many-To-Many only . (but I miss understand for what ). Is it possible to have relation between first_farm and second farm more than one times ?( a Farm_one is filial of Farm_two twice? :slight_smile: )
If yes, just create new Model like FarmRef with $useTable = 'Farms’
One-To-One relation with farms.
If you answer NO.
I think you have to create Model this type but with Many-To-Many relations.

5

Hi, it’s not a parent child relation or a one to one relation.

It should be possible to connect onne farm to many others, because the point is there could be farms which are connected to others, because the owner is the same. Its a many to many relation.

So my problem is that in connected_farms I use belongsTo instead of belongsToMany?

I need a little bit more help because I am new to CakePHP, so could you give me the code examples that I can understand how to do it?

6

The whole thing is confusing me more and more…

className: the class name of the model being associated to the current model. If you’re defining a ‘Article belongsToMany Tag’ relationship, the className key should equal ‘Tags.’
joinTable: The name of the join table used in this association (if the current table doesn’t adhere to the naming convention for belongsToMany join tables). By default this table name will be used to load the Table instance for the join table.
foreignKey: The name of the foreign key that references the current model found on the join table, or list in case of composite foreign keys. This is especially handy if you need to define multiple belongsToMany relationships. The default value for this key is the underscored, singular name of the current model, suffixed with ‘_id’.
bindingKey: The name of the column in the current table, that will be used for matching the foreignKey. Defaults to the primary key.
targetForeignKey: The name of the foreign key that references the target model found on the join model, or list in case of composite foreign keys. The default value for this key is the underscored, singular name of the target model, suffixed with ‘_id’.

I don’t relly understand what is what, and how I have to configure it to my needs, I need examples to understand such things.

7

OK.
I see you have to connect Farm with Farm table, because you need Many-To-Many you need table connected_farms .
The same idea create new Model Farm1s $useTable = 'Farms’
tables farms_farms1 . set for Farms and Farm1s hasAndBelongsToMany associations.

8

Does this solution also solving the problem, that I can connect Farm2 with Farm1 if already Farm1 is connected with Farm2?

9

May be first of all ALTER TABLE table ADD UNIQUE INDEX_NAME ( field1 , field2 ).
And you can write validation rule, or check it in saveBefore.

10

Sry you dont understood my problem. An unique index doesn’t solve it because if you select farm1 in field1 and farm2 in field2 you could add a second data record with farm2 in field1 and farm1 in field2, because an unique index is not swaping the values from the fields. Your solution is the same I presented in my first post.

Now my colleague scripted it out, so that if you connect farms, the script creates automatically a connection between every connected farms vice versa.

12

Yes, you are right. But in any case Cake has custom validation rule , and for second option saveBefore.

I didn’t test mehdim’s proposal, but may be it’s the best advice.

13

You want to connect two objects of the same type ( = Farm) .
The convention says you have to create a join table, named farms_farms . It’s a HABTM relationShip .
In this way you can have only one record to connect two farms. And you can put additionnal information on the jointable.

14

Okay but the problem is how should I name the fields in farms_farms, one field is clearly farm_id, but how is the second one called after convention?

15

I have the same problem, Has given with the solution?, if is so, please tell me what do you did?, thanks

16

Were you able to figure out your problem? I think you’re missing the ‘through’ option in your belongstomany definitions.