如何只选择第一个多对多记录并将其作为单独的关系？

问题描述

我的表格如下：

EventTicketSales 归属ToMany 批准通过 EventTicketSalesApprovings 。

EventTicketSales belongsToMany Approvings via EventTicketSalesApprovings.

内部 EventTicketSalesTable.php 我写了

    $this->belongsToMany('Approvings', [
        'className' => 'Contacts',
        'joinTable' => 'event_ticket_sales_approvings',
        'targetForeignKey' => 'contact_id',
        'saveStrategy' => 'replace',
    ]);

做分页时，我写

$contain = [
        'Approvings'
    ];

$sales = $this->paginate($this->EventTicketSales->find()->where($conditions)->join($join)->contain($contain));

在search.ctp中，我写

Inside the search.ctp, I write

 <?php foreach($eventTicketSales as $eventTicketSale):?>
    <tr>
         <td>
           <?= $eventTicketSale->approvings[0]->company ?>
         </td>
    </tr>

我想要的是

1. 保持联系人表与 event_ticket_sales 表之间的多对多关系作为批准关系


2. 但有一种方法只能查询仅第一个批准

1. keep the many to many relationships between the contacts table and the event_ticket_sales table as Approvings relation,
2. but have a way to only query the first Approving only

为什么？

原因是此查询我只需要在页面中显示第一个批准的联系人并仍保持分页即可。

The reason is this query I only need to show the first approving contact person in the page and still keep pagination.

并且我希望查询尽可能地高效。

and I want the query to be as efficient as I possibly can.

如果有更好的方法可以用联接而不是包含来做到这一点，我将很高兴

If there is a better way to do this with joins instead of contains, I will be happy to do so.

什么是首次批准联系人？

假设我们具有 event_ticket_sales 且有2个批准。第 event_ticket_sales_approvings 表中的 id 最小的是第一个。是的 id 是主整数自动增量。

Suppose we have a event_ticket_sales that have 2 approvings. The one where the id in event_ticket_sales_approvings table is the smallest is the first one. Yes the id is primary integer auto increment.

上述分页查询的条件是什么？

    $conditions = [
                'OR' => [
                    ['EventTicketSales.invoice_number LIKE' => '%'.$data['search_field'].'%'],
                    ['EventTicketSales.receipt_number LIKE' => '%'.$data['search_field'].'%'],
                    ['ApprovingsContacts.company LIKE'      => '%'.$data['search_field'].'%'],
                ]
            ];

更新

我很高兴在这里有一个类似的问题

I appreciate that there is a similar question here How to limit contained associations per record/group?

但是，我想指出：

1. 该问题试图找到文章及其得分最高的摘要。因此，Article具有很多摘要，而Abstract属于Article


2. 在我的案例中，EventTicketSales通过EventTicketSalesApprovings属于ManyTorovings。

推荐答案

我解决类似问题的方法：

Se my approach for solve similar question:

这篇关于如何只选择第一个多对多记录并将其作为单独的关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！