Yii2: Joined Gridview shows fewer records

[lowap]

What steps will reproduce the problem?

ModelQuery:

public function mq() {
    $this->select([
                '*',
                'TRIM(TRAILING \'.\' FROM TRIM(TRAILING \'0\' FROM adn)) AS adn',
                'TRIM(TRAILING \'.\' FROM TRIM(TRAILING \'0\' FROM fdn)) AS fdn',
                'TRIM(TRAILING \'.\' FROM TRIM(TRAILING \'0\' FROM zgn)) AS zgn',
            ])
            ->leftJoin('mq', 'mqid = mq.id');
    return $this;
}

ModelSearch:

public function search($params) {
    $query = Za::find();

    $dataProvider = new ActiveDataProvider([
        'query' => $query->mq(),
        'sort' => ['defaultOrder' => ['aonr' => SORT_DESC]],
        'pagination' => [
            'pageSize' => 15,
        ],
    ]);

What is the expected result?

15 records on one page

What do you get instead?

3, 3, 3, 6, 4, 3 etc. records on one page

Zeige 1-3 von 9.802 Einträgen.

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.11.2
| PHP version | 5.6.25
| Operating system | Windows Server 2012 R2

[TerraSkye]

probably a join fault. (showing less records)

[lowap]

I've checked the (simple) query and it seems to be alright. totalCount is also alright.

[yii-bot]

Thanks for posting in our issue tracker.
In order to properly assist you, we need additional information:

• When does the issue occur?
• What do you see?
• What was the expected result?
• Can you supply us with a stacktrace? (optional)
• Do you have exact code to reproduce it? Maybe a PHPUnit tests that fails? (optional)

Thanks!

_This is an automated comment, triggered by adding the label status:need more info._

[samdark]

Can't be reproduced without tables schema, data model code and exact queries. It would be great if you'll be able to find the reason for it.

[lowap]

I've figured it out that if I'm doing like this:

public function mq() {
    $this->select([
            'za.*',
            ...
        ])
        ->leftJoin('mq', 'mqid = mq.id');
return $this;
}

then it's rendering normally 15 records. Without za. it's not working. I have no clue why I'm absolutely not an expert in oop or yii, sorry.

[TerraSkye]

do u have a group by somewhere?

[lowap]

nope. that's all:

SELECT `za`.*, ... `lwnr`
FROM `za`
LEFT JOIN `mq` ON za.mqid = mq.id
ORDER BY `aonr` DESC
LIMIT 15

[githubjeka]

Probably populate doesn't work correctly....
Can u show rawSql from your $query and executed result of raw sql ?

without za*

[lowap]

rawSql? it's above your comment, isn't it, or do you mean something else? How do you mean executed result?

[TerraSkye]

Do you have an index by on the model?that would cause thrm to overwrite eachother

[lowap]

I don't think so. I'm not that professional to use such things.

[githubjeka]

No.
http://www.yiiframework.com/doc-2.0/yii-db-command.html#getRawSql()-detail

[lowap]

SELECT *, TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM adn)) AS adn, TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM fdn)) AS fdn, TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM zgn)) AS zgn, lwnr FROM za LEFT JOIN mq ON za.mqid = mq.id

[githubjeka]

SELECT *, TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM adn)) AS adn, TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM fdn)) AS fdn, TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM zgn)) AS zgn, lwnr FROM za LEFT JOIN mq ON za.mqid = mq.id

and raw result. From phpmyadmin for example or terminal...

[bizley]

I'm almost sure there are duplicated rows because of the joined relation. Some mq table rows probably are related to the same za table row and because there is no indexBy set in query the default one is taken which is primary key here. Since the same key in array is there more than once every duplication overwrites the previous one hence the less rows displayed per page.

See this line in ActiveQuery.

[lowap]

In mq there are only distinct records. I'm joining on ids, and selecting only lwnr what is a varchar, so it's not the primary key (only a unique index), so it's not the auto incr id of the table.

[bizley]

I'm confused. There is no distinct statement in your query and you select all columns (*). Simple way to check if there are any duplicates is to fetch data using the same query outside of Yii framework or to use Query methods instead of ActiveQuery (so no models) - Query does not remove duplicates if there is no indexBy.

[lowap]

phpmyadmin is forbidden at the moment and I have no idea how can I make it work. Can I do it somehow in Sqlyog?

[samdark]

Yes. With SQL.

[bizley]

I'm not familiar with this one. You can do this in mysql console if you have got access there or call something like this as a test in your Yii application:

$data = Yii::$app->db->createCommand(/* query here */)->queryAll();

and see what is inside $data.

[lowap]

I have za, it's the main table that I'm querying. It has 164 records. I'm left join-ing the table mq (where only distinct values can be found) on the foreign key mqid, and I'm just selecting a varchar from mq. So in the end I have this:

id | a1 | a2 | ... | lwnr
| -- | -- | -- | -- | ---
01 | xx | yy | ... | lw1
02 | ab | cd | ... | lw1
03 | ds | ff | ... | (null)
04 | ee | rr | ... | lw111
05 | gf | tg | ... | (null)

There can't be any duplicates. This is 100% sure.
This is a hell lot of data! Should I paste here all of them?

Yeah! Ids are the same! Of course! If I'm selecting only *, than of course there are both ids from za and mq. If I'm doing like this:

public $zaid;

public function mq() {
    $this->join('LEFT JOIN', 'mq', 'za.mqid = mq.id')
            ->select([
                '*',
                'za.id AS zaid',
                'TRIM(TRAILING \'.\' FROM TRIM(TRAILING \'0\' FROM adn)) AS adn',
                'TRIM(TRAILING \'.\' FROM TRIM(TRAILING \'0\' FROM fdn)) AS fdn',
                'TRIM(TRAILING \'.\' FROM TRIM(TRAILING \'0\' FROM zgn)) AS zgn',
                'lwnr',
    ])->indexBy('zaid');
    return $this;
}

then it's showing 15 records, but the ids are still wrong: they are coming still from mq. Or is indexBy() not meant to be used like this?

Otherwise I should get a warning that id is ambigous, or somehow yii should handle such situations, not only merge the two ids into one, shouldn't it?

[rob006]

then it's showing 15 records, but the ids are still wrong: they are coming still from mq.

Do you have zaid column in mq?

[lowap]

no, I don't, but it's clear now:

Note: It is important to disambiguate column names when building relational queries involving JOIN SQL statements. A common practice is to prefix column names with their corresponding table names.

Until now I haven't done so but now it's time. However as I remember I have read somewhere before (maybe not about yii) that it's a good practice to use simply id, col1, col2, etc.
Yeah I've found it: http://www.yiiframework.com/wiki/227/guidelines-for-good-schema-design/

• DON'T prepend db table names to field names

however it stays clearly there that it's meant to be only for AR, it would be nice to mention that if we want to use JOINs it should be otherwise (are there any applications, that don't use JOINs? or appr. how many percent?)

• DO name a table's own ID column "id"

[SilverFire]

I've failed to reproduce the problem it and I have no ideas why are you facing it.

@lowap in case you still experience this problem, could you help us reproduce this problem with a unit test?