查询匹配()导致重复行和distinct()无法工作

Question

我试图过滤一个表付款由一个字段相关的表发票。

在查询对象上使用函数matching()可以正确筛选，但会导致重复行。该解决方案似乎使用了distinct()，但是调用distinct(Payments.id)会导致查询无效。我在控制器操作中执行以下操作。

$conditions = [
    'Payments.is_deleted =' => false
];
$args = [
    'conditions' => $conditions,
    'contain' => ['Invoices', 'Invoices.Clients'],
];
$payments = $this->Payments->find('all', $args);
if($issuer) {
    // This causes duplicate rows
    $payments->matching('Invoices', function ($q) use ($issuer) {
        return $q->where(['Invoices.issuer_id' => $issuer['id']]);
    });
    // $payments->distinct('Payments.id'); // Causes a mysql error
}

我是否正确地认为distinct()是我所需要的，如果是这样的话，我是否知道它缺少什么来工作呢？

当取消对上面一行的注释时，我会得到以下mysql错误：

错误: SQLSTATE42000:语法错误或访问冲突: SELECT list的1055表达式#8不在GROUP子句中，而是包含非聚合列'InvoicesPayments.id‘，该列在功能上不依赖于GROUP子句中的列；这与sql_mode=only_full_group_by不兼容

完整查询：

SELECT
    PAYMENTS.ID AS `PAYMENTS__ID`,
    PAYMENTS.CREATED AS `PAYMENTS__CREATED`,
    PAYMENTS.MODIFIED AS `PAYMENTS__MODIFIED`,
    PAYMENTS.DATE_REGISTERED AS `PAYMENTS__DATE_REGISTERED`,
    PAYMENTS.USER_ID AS `PAYMENTS__USER_ID`,
    PAYMENTS.AMOUNT AS `PAYMENTS__AMOUNT`,
    PAYMENTS.IS_DELETED AS `PAYMENTS__IS_DELETED`,
    INVOICESPAYMENTS.ID AS `INVOICESPAYMENTS__ID`,
    INVOICESPAYMENTS.INVOICE_ID AS `INVOICESPAYMENTS__INVOICE_ID`,
    INVOICESPAYMENTS.PAYMENT_ID AS `INVOICESPAYMENTS__PAYMENT_ID`,
    INVOICESPAYMENTS.PART_AMOUNT AS `INVOICESPAYMENTS__PART_AMOUNT`,
    INVOICES.ID AS `INVOICES__ID`,
    INVOICES.CREATED AS `INVOICES__CREATED`,
    INVOICES.MODIFIED AS `INVOICES__MODIFIED`,
    INVOICES.IS_PAID AS `INVOICES__IS_PAID`,
    INVOICES.IS_DELETED AS `INVOICES__IS_DELETED`,
    INVOICES.CLIENT_ID AS `INVOICES__CLIENT_ID`,
    INVOICES.ISSUER_ID AS `INVOICES__ISSUER_ID`,
    INVOICES.NUMBER AS `INVOICES__NUMBER`,
    INVOICES.SUBTOTAL AS `INVOICES__SUBTOTAL`,
    INVOICES.TOTAL AS `INVOICES__TOTAL`,
    INVOICES.DATE_REGISTERED AS `INVOICES__DATE_REGISTERED`,
    INVOICES.CURRENCY AS `INVOICES__CURRENCY`,
    INVOICES.RECEIVER_NAME AS `INVOICES__RECEIVER_NAME`,
    INVOICES.RECEIVER_RFC AS `INVOICES__RECEIVER_RFC`,
    INVOICES.EMAIL_SENDER AS `INVOICES__EMAIL_SENDER`,
    INVOICES.PDF_PATH AS `INVOICES__PDF_PATH` 
FROM
    PAYMENTS PAYMENTS 
    INNER JOIN
        INVOICES_PAYMENTS INVOICESPAYMENTS 
        ON PAYMENTS.ID = (
            INVOICESPAYMENTS.PAYMENT_ID
        )
    INNER JOIN
        INVOICES INVOICES 
        ON (
            INVOICES.ISSUER_ID = :C0 
            AND INVOICES.ID = (
                INVOICESPAYMENTS.INVOICE_ID
            )
        ) 
WHERE
    (
        PAYMENTS.IS_DELETED = :C1 
        AND PAYMENTS.DATE_REGISTERED >= :C2 
        AND PAYMENTS.DATE_REGISTERED <= :C3
    )
GROUP BY
    PAYMENT_ID 
ORDER BY
    PAYMENTS.DATE_REGISTERED ASC

Answer 1

这种行为是预期的，因为匹配将使用INNER连接，是的，分组是避免重复的方法：

由于此函数将创建内部联接，您可能需要考虑在find查询上调用distinct，因为如果条件尚未排除重复行，则可能会得到重复行。例如，当相同的用户对一篇文章进行多次评论时，情况可能会是这样。

Cookbook > Database Access & ORM > Query Builder > Loading Associations > Filtering by Associated Data

正如错误消息所述，您的MySQL服务器被配置为使用严格的模式，其中查询无效。您可以禁用严格模式正如Akash prajapati提到的 (这可能会带来自己的问题，因为随后允许MySQL随意选择组的值)，或者您可以更改查询方式以符合严格模式。

在您需要对主键进行分组的情况下，只需切换到使用innerJoinWith()，而不像matching()，这将不会将关联的任何字段添加到SELECT列表中，而且在严格模式下一切都应该很好，因为所有其他内容在功能上都是依赖的：

在对某个键进行分组以破坏功能依赖检测的情况下，解决这一问题的一种方法是使用子查询进行筛选，其中一个只选择该键的查询，如下所示：

$conditions = [
    'Payments.is_deleted =' => false
];

$payments = $this->Payments
    ->find()
    ->contain(['Invoices.Clients']);

if($issuer) {
    $matcherQuery = $this->Payments
        ->find()
        ->select(['Payments.some_other_field'])
        ->where($conditions)
        ->matching('Invoices', function ($q) use ($issuer) {
            return $q->where(['Invoices.issuer_id' => $issuer['id']]);
        })
        ->distinct('Payments.some_other_field');

    $payments->where([
        'Payments.some_other_field IN' => $matcherQuery
    ]);
} else {
    $payments->where($conditions);
}

这将导致类似于此的查询，然后外部查询可以选择您想要的所有字段：

SELECT
    ...
FROM
    payments
WHERE
    payments.some_other_field IN (
        SELECT
            payments.some_other_field
        FROM
            payments
        INNER JOIN
            invoices_payments ON
                payments.id = invoices_payments.payment_id
        INNER JOIN
            invoices ON
                invoices.issuer_id = ...
                AND
                invoices.id = invoices_payments.invoice_id
        WHERE
            payments.is_deleted = ...
        GROUP BY
            payments.some_other_field
    )

Answer 2

mysql中sql_mode值的问题，因此您需要将sql_mode值设置为空，然后您可以尝试并正常工作。

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

请告诉我还有其他的事。