My Tables are as follows:
EventTicketSales
belongsToMany Approvings
via EventTicketSalesApprovings
.
Inside EventTicketSalesTable.php
I wrote
$this->belongsToMany('Approvings', [
'className' => 'Contacts',
'joinTable' => 'event_ticket_sales_approvings',
'targetForeignKey' => 'contact_id',
'saveStrategy' => 'replace',
]);
When I do pagination, I write
$contain = [
'Approvings'
];
$sales = $this->paginate($this->EventTicketSales->find()->where($conditions)->join($join)->contain($contain));
Inside the search.ctp, I write
<?php foreach($eventTicketSales as $eventTicketSale):?>
<tr>
<td>
<?= $eventTicketSale->approvings[0]->company ?>
</td>
</tr>
What I would like is
- keep the many to many relationships between the contacts table and the
event_ticket_sales
table as Approvings relation, - but have a way to only query the first Approving only
Why?
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.
What defines as first approving contact person?
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.
What are the conditions in the above pagination query?
$conditions = [
'OR' => [
['EventTicketSales.invoice_number LIKE' => '%'.$data['search_field'].'%'],
['EventTicketSales.receipt_number LIKE' => '%'.$data['search_field'].'%'],
['ApprovingsContacts.company LIKE' => '%'.$data['search_field'].'%'],
]
];
UPDATE
I appreciate that there is a similar question here How to limit contained associations per record/group?
However, I want to state that:
- that question is trying to find the Article and its highest scoring Abstract. therefore Article hasMany Abstract and Abstract belongsTo Article
- In my case, the EventTicketSales belongsToMany Approvings via EventTicketSalesApprovings. Which is different.