Laravel 多对多分组查询自带外键和本地建报错问题

我想在关联时返回得是多对多关联关系得分组数据,不知道能否实现!在尝试对多对多分组时,执行得查询sql都会自带关联键,我想知道有没有方法隐藏或者是不查询关联字段?

模型关联代码

public function orderEstatePoints()
    {
        return $this->belongsToMany(EstatePoint::class, 'order_estate_points')
            ->select([
                'estate_points.position_id',
                DB::raw('sum(use_number) as use_number_sum')
            ])->groupBy(['estate_points.position_id']);
    }

下面是我打印得sql

SELECT
    `m_estate_points`.`position_id`,
    sum( use_number ) AS use_number_sum,
    `m_order_estate_points`.`estate_id` AS `pivot_estate_id`,
    `m_order_estate_points`.`estate_point_id` AS `pivot_estate_point_id` 
FROM
    `m_estate_points`
    INNER JOIN `m_order_estate_points` ON `m_estate_points`.`id` = `m_order_estate_points`.`estate_point_id` 
WHERE
    `m_order_estate_points`.`estate_id` IN ( 1 ) 
    AND `m_estate_points`.`deleted_at` IS NULL 
GROUP BY
    `m_estate_points`.`position_id`

最后是我报错信息

    "message": "SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'media.m_order_estate_points.estate_point_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `m_estate_points`.`position_id`, sum(use_number) as use_number_sum, `m_order_estate_points`.`estate_id` as `pivot_estate_id`, `m_order_estate_points`.`estate_point_id` as `pivot_estate_point_id` from `m_estate_points` inner join `m_order_estate_points` on `m_estate_points`.`id` = `m_order_estate_points`.`estate_point_id` where `m_order_estate_points`.`estate_id` in (1) and `m_estate_points`.`deleted_at` is null group by `m_estate_points`.`position_id`)",
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
讨论数量: 3

我查了,说这么改

数据库配置文件

'strict' => false,
4年前 评论

@likunyan 非常感谢 终于找到了方向 我晚上再尝试下 白天上班 :joy:

4年前 评论

@likunyan 设置mysql数据库得严格模式是一种解决方式,只是在非严格模式下不会报错了。但是不太算是从根本上解决这问题,在关联中关联建只是用来关联得,而非用一定要在查询中查出来吧!个人觉得这是个问题。我查看了源码这关联建是作为多对多默认字段得

多对多默认字段源码

namespace Illuminate\Database\Eloquent\Relations;\

use Illuminate\Support\Str;\
use InvalidArgumentException;\
use Illuminate\Database\Eloquent\Model;\
use Illuminate\Database\Eloquent\Builder;\
use Illuminate\Database\Eloquent\Collection;\
use Illuminate\Database\Eloquent\ModelNotFoundException;\

class BelongsToMany extends Relation
{

    ...

    protected function aliasedPivotColumns()
    {
        $defaults = [$this->foreignPivotKey, $this->relatedPivotKey];

        return collect(array_merge($defaults, $this->pivotColumns))->map(function ($column) {
            return $this->table.'.'.$column.' as pivot_'.$column;
        })->unique()->all();
    }

下面是我修改了严格模式后得sql(稍有改动)

SELECT
    `m_estate_points`.`position_id`,
    `m_estate_points`.`estate_id`,
    sum( use_number ) AS use_number_sum,
    sum( type_total ) AS type_total_sum,
    `m_order_estate_points`.`estate_id` AS `pivot_estate_id`,
    `m_order_estate_points`.`estate_point_id` AS `pivot_estate_point_id` 
FROM
    `m_estate_points`
    INNER JOIN `m_order_estate_points` ON `m_estate_points`.`id` = `m_order_estate_points`.`estate_point_id` 
WHERE
    `m_order_estate_points`.`estate_id` IN ( 1 ) 
    AND `m_estate_points`.`deleted_at` IS NULL 
GROUP BY
    `m_estate_points`.`position_id`,
    `m_estate_points`.`estate_id`
4年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!