时间戳

首页 > 解决方案 > 查询匹配（）导致重复行和不同（）不起作用

mysql - 查询匹配（）导致重复行和不同（）不起作用

问题描述

我正在尝试通过关联表发票上的字段过滤一个表付款。

在查询对象上使用该函数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 错误：

错误：SQLSTATE[42000]：语法错误或访问冲突：1055 SELECT 列表的表达式 #8 不在 GROUP BY 子句中，并且包含在功能上不依赖于 GROUP BY 子句中的列的非聚合列“InvoicesPayments.id”；这与 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

标签： mysqlcakephpgroup-bydistinctcakephp-3.x

解决方案

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

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

Cookbook > Database Access & ORM > Query Builder > Loading Associations > 按关联数据过滤

如错误消息所述，您的 MySQL 服务器配置为使用严格only_full_group_by模式，您的查询无效。您可以禁用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
    )

推荐阅读