mysql 查询匹配()导致重复行，并且distinct()不起作用

wmtdaxz3 于 2023-01-29 发布在 Mysql

关注(0)|答案(3)|浏览(357)

我正尝试通过关联表Invoices上的字段筛选一个表Payments。
在查询对象上使用函数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 1055 SELECT列表的表达式#8不在GROUP BY子句中，并且包含非聚集列'InvoicesPayments.id'，该列在功能上不依赖于GROUP BY子句中的列;这与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

mysql

来源：https://stackoverflow.com/questions/61928725/query-matching-causing-duplicate-rows-and-distinct-not-working

3条答案

按热度按时间

wbgh16ku1#

这种行为是意料之中的，因为匹配将使用INNER连接，而且分组是避免重复的方法：
由于此函数将创建一个INNER JOIN，您可能需要考虑在查找查询中调用distinct，因为如果您的条件尚未排除重复行，则可能会得到重复行。例如，当相同的用户对一篇文章进行多次评论时，可能会出现这种情况。

Cookbook〉数据库访问和ORM〉Query Builder〉加载关联〉按关联数据筛选

正如错误消息所指出的，您的MySQL服务器被配置为使用strict only_full_group_by mode，因此您的查询是无效的。您可以禁用strict模式as mentioned by Akash prajapati（这可能会带来一些问题，因为MySQL可以随机选择一个组的值），或者您可以更改查询方式以符合strict模式。
在需要对主键进行分组的情况下，可以简单地切换到使用innerJoinWith()，与matching()不同的是，它不会将该关联的任何字段添加到SELECT列表中，并且在strict模式下应该没问题，因为其他一切都是功能相关的：
如果你要对一个键进行分组，而这个键会破坏函数依赖检测，那么解决这个问题的一个方法就是使用一个子查询进行过滤，这个子查询只选择那个键，沿着如下：

$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
    )

赞(0）回复(0）举报 2023-01-29

c0vxltue2#

mysql中sql_mode值的问题，所以你需要将sql_mode值设置为空，然后你可以尝试并为你工作。

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

还有什么事请告诉我。

赞(0）回复(0）举报 2023-01-29

u5rb5r593#

我也遇到了同样的问题，但是我太害怕设置@Akash提到的sql_mode，也太急于重构查询，所以我决定使用继承的Collection方法indexBy（）
https://book.cakephp.org/4/en/core-libraries/collections.html#Cake\Collection\Collection::indexBy
$resultSetFromYourPaymentsQuery = $resultSetFromYourPaymentsQuery->indexBy('id');
它工作起来很有魅力，而且是DB独立的。
编辑：经过更多的修改，这可能并不适用于所有用例。按照公认答案中的建议将matching替换为innerJoinWith可能会以更普遍的方式解决这个问题。

赞(0）回复(0）举报 2023-01-29

我来回答

mysql 查询匹配()导致重复行，并且distinct()不起作用

3条答案

相关问题

热门标签

最新问答