Limit doesn't work well in yii2 query

Question

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();

but this code will select just 5 records,

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();

The modified code will return 20 records.

UPDATE1:

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

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

what happens if you remove the `select` part and select everything i recon it will show you correct number of records? — Muhammad Omer Aslam, Apr 12 '18 at 01:24

rob006 · Answer 1 · 2018-04-11T16:10:00.863

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.

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.

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

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

Limit doesn't work well in yii2 query

1 Answers1

Linked