我从俱乐部会员数据库中提取了两种类型的会员;我有两个自定义查找器方法来提取这些。这些查找器工作正常。第三个自定义查找器调用其他两个并使用Union语句创建单个结果集。结果集的内容是正确的。然而，在索引中使用分页。ctp显示完整的结果集然后重复第二个查找器的内容。联合结果集不包含重复的内容。看起来Paginate被联合结果弄糊涂了。

• • 第三个自定义查找器代码：**

public function findMembersByBadAddress(Query $query, array $options=[])
{
    // Find members records with bad email or mailing addresses 
    // Specify "mailing" or "email" as the "Address" option to select either bad mailing addresses or bad
    // email addresses

    // Specific fields are retrieved so both resultset have the same number of columns. This allows the 
    // Union to work.
    $fieldsToKeep = ['Members.id','Members.first_name','Members.middle_name',
        'Members.last_name','Members.suffix', 'Members.date_joined'];

    if( $options['Address'] == 'mailing' ) {
        $flag = ['MailingAddresses.is_invalid' => true];
        $contain = ['MailingAddresses'];
    } else {
        $flag = ['EmailAddresses.is_invalid' => true];
        $contain = ['EmailAddresses'];
    }

    $query = $this->find('currentFullMembers');
    $query->contain($contain);
    $query->Select($fieldsToKeep);
    $query->where($flag);
    //Log::write('debug', "Count from MembersTable:Full = " . $query->count());

    $flagQuery2 = $this->find('lifetimeMembers');
    $flagQuery2->contain($contain);
    $flagQuery2->Select($fieldsToKeep);
    $flagQuery2->where($flag);
    //Log::write('debug', "Count from MembersTable:Lifetime = " . $flagQuery2->count());

    $query->unionAll($flagQuery2);
    $query->epilog("ORDER BY Members__last_name ASC, Members__first_name ASC");
    //Log::write('debug', "Count from MembersTable = " . $query->count());

    return $query;
}

字符串

• • 来自控制器：**

case ('M'): // bad mailing addresses  DONE
                $query = $this->Members->find('membersByBadAddress', ['Address' => 'mailing']);
                $this->set('reportTitle', 'Bad Mailing Addresses');
                break;

型
...

$this->set('members', $this->paginate($query, ['limit' => $pageLimit]) );

型

• • 来自index. ctp**

<table cellpadding="0" cellspacing="0" class='table-condensed'>
            <thead>
                <tr>
                    <th scope="col"><?= $this->Paginator->sort('id') ?></th>
                    <th scope="col"><?= $this->Paginator->sort('first_name') ?></th>
                    <th scope="col"><?= $this->Paginator->sort('middle_name') ?></th>
                    <th scope="col"><?= $this->Paginator->sort('last_name') ?></th>
                    <th scope="col"><?= $this->Paginator->sort('suffix') ?></th>
                    <?php
                        if( $displayJoinDate ) {
                            echo "<th scope='col'>" . $this->Paginator->sort('Date Joined') . "</th>";
                        }
                    ?>
                    <th scope="col" class="actions"><?= __('Actions') ?></th>
                </tr>
                </thead>
                <tbody>
                <?php
        //          Log::write('debug', "Count = " . $members->count());
                     foreach($members as $member) {
                    //   Log::write("debug", $member->first_name . ' ' . $member->last_name);
                        echo "<tr>";
                        echo "<td>" . $this->Number->format($member->id) . "</td>";
                        echo "<td>" . h($member->first_name) . "</td>";
                        echo "<td>" . h($member->middle_name) . "</td>";
                        echo "<td>" . h($member->last_name) . "</td>";
                        echo "<td>" . h($member->suffix) . "</td>";
                        if( $displayJoinDate ) {
                           echo "<td>" . h($member->date_joined) . "</td>";
                        }
                        echo '<td class="actions">';
                            echo $this->Html->link(__('View'), ['action' => 'view', $member->id]);
                            echo $this->Html->link(__('Edit'), ['action' => 'edit', $member->id]);
                            echo $this->Html->link(__('Renew'), ['controller' => 'Dues2', 'action' => 'add', $member->id]);
                     //      <?= $this->Form->postLink(__('Delete'), ['action' => 'delete', $id], 
                     //             ['confirm' => __('Are you sure you want to delete # {0}?', $id)]) 
                        echo "</td>";
                    echo "</tr>";
             }
                ?>
            </tbody>
        </table>

型
有什么办法吗？

• • 更新**

我怀疑这个问题是由第一个SELECT语句后的LIMIT 20 OFFSET 0而不是UNION后的LIMIT 20 OFFSET 0引起的。下面是SQL日志中显示的SQL语句：

SELECT

型
后缀. suffix AS Suffixes__suffix FROM后缀
（选择www.example.com AS Members__id，Members. first_name AS Members__first_name，Members. middle_name AS Members__middle_name，Members. last_name AS Members__last_name，Members. suffix AS Members__suffix，www.example.com_加入为Members__date_joined从成员成员INNER加入dues2 Dues2 ON（成员. member_type ='1' AND Dues2.payment_category = 2 AND Dues2.created>='2017 - 10 - 1 0 00：00：00'和Dues2.created <='2018 - 12 - 31 23：59：59' AND www.example.com =（Dues2.member_id））LEFT JOIN members SocialMembers ON（SocialMembers. member_type = 2 AND www.example.com =（SocialMembers. full_member_id）左加入电子邮件_地址EmailMail邮件地址EmailMail邮件地址Email邮件地址Email地址Email Members. first_name ASC LIMIT 20 OFFSET 0）UNION ALL（SELECT www.example.com AS Members__id，Members. first_name AS Members__first_name，Members. middle_name AS Members__middle_name，Members. last_name AS Members__last_name，Members. suffix AS Members__suffix，www.example.com_加入为Members__date_joined从成员成员离开加入成员SocialMembers ON（SocialMembers. member_type = 2 AND www.example.com_加入为Members__date_joined从成员成员离开加入成员SocialMembers ON（SocialMembers. member_type = 2 AND www.example.com_加入为Members__date_joined从成员离开加入成员SocialMembers. example.com =（SocialMembers. full_member_id））LEFT JOIN email_addresses Emails.id =（Emails.member_id）WHERE（www.example.com_deceased = 0 AND Members. member_type = 4 AND www.example.com_invalid = 1）ORDER BY Members. last_name ASC，Members. first_name ASC）ORDER BY Members__last_name ASC，Members__first_name ASC