dongsuikai8286
2018-09-27 10:47 阅读 36
已采纳

CakePHP 3.6.11:连接表的Where子句

I have these 3 tables:

customers:

customerstable

services:

servicestable

customerservices:

customerservicestable

With this relation in CustomerservicesTable.php:

$this->belongsTo('Customers')
            ->setForeignKey('customerid');

$this->belongsTo('Services')
            ->setForeignKey('serviceid');

In Customers edit page I want to add a table with the Services of the specific customer (and then add new, edit existing etc).

So in Template\Customers\edit.ctp I have this table:

<h3><?= __('Services') ?></h3>
    <table cellpadding="0" cellspacing="0">
        <thead>
            <tr>
                <th scope="col"><?= $this->Paginator->sort('Date') ?></th>
                <th scope="col"><?= $this->Paginator->sort('Service') ?></th>
                <th scope="col"><?= $this->Paginator->sort('Price') ?></th>
                <th scope="col" class="actions"><?= __('Actions') ?></th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($services as $service): ?>
            <tr>
                <td><?= h($service->created) ?></td>
                <td><?= h($service->title) ?></td>
                <td><?= $this->Number->format($service->price) ?></td>
                <td class="actions">
                    <?= $this->Html->link(__('View'), ['action' => 'view', $customer->id]) ?>
                    <?= $this->Html->link(__('Edit'), ['action' => 'edit', $customer->id]) ?>
                    <?= $this->Form->postLink(__('Delete'), ['action' => 'delete', $customer->id], ['confirm' => __('Are you sure you want to delete # {0}?', $customer->id)]) ?>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>

and in the edit function of Controller\CustomersController.php I have added these lines:

//Get customerservices for specific customer
        $servicesTable = TableRegistry::get('Services');
        $services = $servicesTable->find('all');//->where(['Services.Customerservices.id =' => $this->data['Customers']['id']]);

        $this->set(compact('services'));

And I have commented the where part. How I can change it in order to get only the services that belong to the specific customer? using the customerservicesTable?

And after that can I edit directly the CustomerservicesController.php to implement the add,edit functions of this table?

EDIT

After ndm suggestion I changed it like this:

//Get customerservices for specific customer
        $servicesTable = TableRegistry::get('Services');
        $services = $servicesTable->find()->matching('Customerservices', function(\Cake\ORM\Query $q) {
                return $q->where(['Customerservices.customerid' => $this->data['Customers']['id']]);
            });

But its not working. Probably the $this->data['Customers']['id'] is not working because if I replace it with 1 (the Customer id) its working as expected. Any idea why is not working?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    dpjuppr1361 dpjuppr1361 2018-09-27 13:37

    Try this instead:

    $servicesTable = TableRegistry::get('Services');
    
    $customerId = $this->data['Customers']['id'];
    // how about $this->request->getData('Customer.id') ?
    
    // pass variable to function with `use`
    $services = $servicesTable->find()->matching('Customerservices', function(\Cake\ORM\Query $q) use ($customerId) {
        return $q->where(['Customerservices.customerid' => $customerId]);
    });
    

    If you are not sure that $this->data['Customers']['id'] contains what you expect, just have a look what’s in there:

    debug($this->data['Customers']);
    
    // or
    debug($this->data);
    
    // or
    print_r($this->data['Customers']);
    

    If this is data published by the Request Object, have a look here: https://book.cakephp.org/3.0/en/controllers/request-response.html#request-body-data

    // An input with a name attribute equal to 'MyModel[title]' is accessible at
    $title = $this->request->getData('MyModel.title');
    
    点赞 评论 复制链接分享
  • doupu5941 doupu5941 2018-09-27 13:36

    I fixed this by adding use ($id) and $id instead of $this->data['Customers']['id'] :

    //Get customerservices for specific customer
            $servicesTable = TableRegistry::get('Services');
            $services = $servicesTable->find()->matching('Customerservices', function(\Cake\ORM\Query $q) use ($id) {
                    return $q->where(['Customerservices.customerid' => $id]);
                });
    
    点赞 评论 复制链接分享

相关推荐