How to correctly save data in a BelongsToMany Associations?

Question

My app manages repair orders for an electronic products workshop. when I try to save several identical items (with the same id), cake keeps only the last entry, ignoring previous ones. (In my example, the last product)

I was trying to configure the "saveStrategy" in the model, trying with "append", but I had no results. When I enter different products (with different ids), it saves it with no problems.

Tables:

Tickets

| id | client_id | reference |

Products

| id | code | description |

Products_Tickets

| id | ticket_id | product_id | qty | serial_number | notes |

---

debug($this->request->getData());

[
    'client_id' => '1',
    'reference' => 'Client reference',
    'products' => [
        (int) 0 => [
            'id' => '1',
            '_joinData' => [
                'qty' => '1',
                'serial_number' => '123',
                'notes' => 'Not working'
            ]
        ],
        (int) 1 => [
            'id' => '1',
            '_joinData' => [
                'qty' => '1',
                'serial_number' => '345',
                'notes' => 'Not Working too'
            ]
        ],
        (int) 2 => [
            'id' => '1',
            '_joinData' => [
                'qty' => '1',
                'serial_number' => '567',
                'notes' => 'Note number 3'
            ]
        ],
        (int) 3 => [
            'id' => '1',
            '_joinData' => [
                'qty' => '1',
                'serial_number' => '978',
                'notes' => 'Another note'
            ]
        ]
    ]
]

It supposed to save is all the data, not only the last value.

How do I solve this problem?

Answer 1

If I understand your business case correctly, you are creating a ticket for a client and in the same transaction you are adding 4 products to the ticket. So you are adding a ticket with 4 associated product_tickets (the items on the ticket).

In the model ProductsTable.php

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

        $this->setTable('products');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->addBehavior('Timestamp');

        $this->hasMany('ProductTickets', [
            'foreignKey' => 'product_id'
        ]);
    }

In the model TicketsTable.php

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

        $this->setTable('tickets');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->addBehavior('Timestamp');

        $this->hasMany('ProductTickets', [
            'foreignKey' => 'ticket_id'
        ]);
    }

In the model ProductTicketsTable.php

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

        $this->setTable('product_tickets');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');

        $this->addBehavior('Timestamp');

        $this->belongsTo('Tickets', [
            'foreignKey' => 'ticket_id',
            'joinType' => 'INNER'
        ]);
        $this->belongsTo('Products', [
            'foreignKey' => 'product_id',
            'joinType' => 'INNER'
        ]);
    }

Then you can use the Tickets controller only and save with associated ProductTickets (the ticket line items).

So, in src\Template\Tickets\add.php, you can set up the form like this:

    <?= $this->Form->create($ticket) ?>
    <fieldset>
        <legend><?= __('Add Ticket') ?></legend>
        <?php
            echo $this->Form->control('client');
            echo $this->Form->control('reference');
        ?>
    </fieldset>
    <fieldset>
        <legend><?= __('Add Product_Tickets (Ticket items)') ?></legend>
       <table>
            <thead>
                <tr>
                    <th><?= __('Product') ?></th>
                    <th><?= __('Qty') ?></th>
                    <th><?= __('Serial Number') ?></th>
                    <th><?= __('Notes') ?></th>
                </tr>
            </thead>

            <tbody>
                <tr>
                <td>
                <?php echo $this->Form->control("product_tickets.0.product_id", []) ?>
                </td>   
                <td>
                <?php echo $this->Form->control("product_tickets.0.qty", []) ?>
                </td>
                <td>
                <?php echo $this->Form->control("product_tickets.0.serial_number", []) ?>
                </td>
                <td>
                <?php echo $this->Form->control("product_tickets.0.notes", []) ?>
                </td>
                </tr>
                <tr>
                <td>
                <?php echo $this->Form->control("product_tickets.1.product_id", []) ?>
                </td>   
                <td>
                <?php echo $this->Form->control("product_tickets.1.qty", []) ?>
                </td>
                <td>
                <?php echo $this->Form->control("product_tickets.1.serial_number", []) ?>
                </td>
                <td>
                <?php echo $this->Form->control("product_tickets.1.notes", []) ?>
                </td>
                </tr>
                <tr>
                <td>
                <?php echo $this->Form->control("product_tickets.2.product_id", []) ?>
                </td>   
                <td>
                <?php echo $this->Form->control("product_tickets.2.qty", []) ?>
                </td>
                <td>
                <?php echo $this->Form->control("product_tickets.2.serial_number", []) ?>
                </td>
                <td>
                <?php echo $this->Form->control("product_tickets.2.notes", []) ?>
                </td>
                </tr>
                <tr>
                <td>
                <?php echo $this->Form->control("product_tickets.3.product_id", []) ?>
                </td>   
                <td>
                <?php echo $this->Form->control("product_tickets.3.qty", []) ?>
                </td>
                <td>
                <?php echo $this->Form->control("product_tickets.3.serial_number", []) ?>
                </td>
                <td>
                <?php echo $this->Form->control("product_tickets.3.notes", []) ?>
                </td>
                </tr>


            </tbody>
            <tfoot>

            </tfoot>
        </table>
    </fieldset>
    <?= $this->Form->button(__('Submit')) ?>
    <?= $this->Form->end() ?>

*(Note, there other/better ways to make forms with multiple associated records.)

In the Tickets controller, in the add method:

    public function add()
    {
        $ticket = $this->Tickets->newEntity();     
        if ($this->request->is('post')) {
            $data = $this->request->getData();
            $ticket = $this->Tickets->newEntity($data, [
                'associated' => ['ProductTickets']
            ]); 
            $ticket = $this->Tickets->patchEntity($ticket, $this->request->getData(),['associated' => 'ProductTickets'
              ]);
            if ($this->Tickets->save($ticket)) {
                $this->Flash->success(__('The ticket has been saved.'));

                return $this->redirect(['action' => 'index']);
            }
            $this->Flash->error(__('The ticket could not be saved. Please, try again.'));
        }
        $products = $this->Tickets->ProductTickets->Products->find('list');
        $this->set(compact('ticket', 'products'));
    }

This works for me and should save any combination of product_ids associated to the ticket_id. For example this request data saves as expected:

debug($this->request->getData());
[
    'client' => '1',
    'reference' => 'reference 1',
    'product_tickets' => [
        (int) 0 => [
            'product_id' => '1',
            'qty' => '12',
            'serial_number' => 'abc1234',
            'notes' => 'note1'
        ],
        (int) 1 => [
            'product_id' => '2',
            'qty' => '5',
            'serial_number' => '4321cba',
            'notes' => 'note2'
        ],
        (int) 2 => [
            'product_id' => '1',
            'qty' => '6',
            'serial_number' => 'a4b3c21',
            'notes' => 'note3'
        ],
        (int) 3 => [
            'product_id' => '3',
            'qty' => '21',
            'serial_number' => '4c3b2a1',
            'notes' => 'note4'
        ]
    ]
]

Per the manual, Saving Has Many Associations

Important note, if any of the 4 products you plan to save on the ticket is NULL, then you'll need to remove that product from the request data. You can do this with a beforeMarshal method in the ProductTicketsTable model.

Hope this is what you had in mind and is helpful.

Cheers,

D.