限制在 yii2 查询中不起作用 [英] Limit doesn't work well in yii2 query

问题描述

我想在我的数据库中选择 20 条记录，所以我使用以下代码来做到这一点:

I want to select 20 record in my database so I use this code to do that:

$query = Course::find()
                        ->alias("t")
                        ->select([
                            't.id', 't.subtitle', 't.title',
                            't.info', 't.skill_level_id', 't.special',
                            't.created', 't.modified', 't.price',
                            't.training_type_id', 't.media_id', 't.instructor_id',
                            't.extension_type'
                        ])
                        ->where(["t.deleted" => 0])
                        ->joinWith([
                            'skillLevel', "courseTarget", "requirement", 'categoryCourses', "media",
                            "instructor", "trainingType"
                        ]);
        $query->limit(20);
        return $query->all();

但是这段代码只会选择 5 条记录，

but this code will select just 5 records,

当我删除 joinWith 部分时，我的代码工作正常并选择了 20 条记录.修改代码:

When I remove the joinWith part, my code works fine and select 20 records. Modified code:

   $query = Course::find()
                    ->alias("t")
                    ->select([
                        't.id', 't.subtitle', 't.title',
                        't.info', 't.skill_level_id', 't.special',
                        't.created', 't.modified', 't.price',
                        't.training_type_id', 't.media_id', 't.instructor_id',
                        't.extension_type'
                    ])
                    ->where(["t.deleted" => 0]);
    $query->limit(20);
    return $query->all();

修改后的代码将返回 20 条记录.

The modified code will return 20 records.

更新 1:

当我删除 limit(20) 时，它会返回 496 条记录，但是当我添加 limit(20) 时，它只返回 5.

When I remove the limit(20) it will return 496 records but when I added limit(20) It just returns 5.

$query = Course::find()
                        ->alias("t")
                        ->select([
                            't.id', 't.subtitle', 't.title',
                            't.info', 't.skill_level_id', 't.special',
                            't.created', 't.modified', 't.price',
                            't.training_type_id', 't.media_id', 't.instructor_id',
                            't.extension_type'
                        ])
                        ->where(["t.deleted" => 0])
                        ->joinWith([
                            'skillLevel', "courseTarget", "requirement", 'categoryCourses', "media",
                            "instructor", "trainingType"
                        ]);
        return $query->all()

此代码工作正常并返回所有内容，但 limit() 查询会使响应错误

This code works fine and returns everything but the limit() query will made the response wrong

推荐答案

这可能是因为 Course 与多个项目有 hasMany 关系.因此，如果您有带有 2 个 requirement 的 Course，则此类查询将为每个 requirement 返回带有重复的 Course 数据的 2 行>.重复的数据合并为一个Course模型，因此2行成为一个模型.这正是这里发生的情况 - 查询返回 20 行，但它只有 5 个唯一的 Course 模型.

This is probably because Course have hasMany relation with multiple items. So if you have Course with 2 requirements, such query will return 2 rows with duplicated Course data for each requirement. Duplicated data is merged into one Course model, so 2 rows becomes one model. This is exactly what is happened here - query returns 20 rows, but it has only 5 unique Course models.

如果不需要访问SQL查询中的关系(例如过滤)，可以替换joinWith() 和 with() - 它不会对 SQL 查询执行 JOIN，只为关系注册急切加载.您可以阅读有关这两种方法之间差异的更多信息 此处.

If you don't need to access relations in SQL query (for example for filtering), you can replace joinWith() with with() - it will not perform JOIN on SQL query, only register eager loading for relations. You can read more about differences between these two methods here.

如果您需要此 JOIN，您可能应该按 Course ID 对结果进行分组，以避免出现重复的 Course 行.

If you need this JOIN you should probably group results by Course ID, to avoid duplicated Course rows.

$query->groupBy('t.id');

这篇关于限制在 yii2 查询中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！