Uncaught Error: Model is not found in collection by index.

[viper]

Hello,
I got a list of leads (32 entries) and trying to scroll down in the list view by pressing "More (12)". And nothing happens. There are no application logs on the server (the log file is clean) and the client console shows the following error message:

Uncaught Error: Model is not found in collection by index.
at child.switchToModelByIndex (eval at _execute (loader.js:94), <anonymous>:972:23)
at child.eval (eval at _execute (loader.js:94), <anonymous>:1040:26)
at Object.<anonymous> (jquery-2.1.4.min.js:2)
at j (jquery-2.1.4.min.js:2)
at Object.fireWith [as resolveWith] (jquery-2.1.4.min.js:2)
at x (jquery-2.1.4.min.js:4)
at XMLHttpRequest.<anonymous> (jquery-2.1.4.min.js:4)

In my case the error is easily to reproduce. It just happens every time. I don't know how to reproduce it on other systems. I got Espo 5.7.9.

Any hints in which direction I should move to solve the issue? Thanks in advance.

[viper]

After doing some research I found out that this issue has nothing to do with the frontend, but is only a backend issue.

Espo generates two different SQL requests for pagination. The only difference between them is "LIMIT 20, 20" or "LIMIT 0, 20" at the end of the sql query.

So the sample requests could be (after some simplification):

Request 1:

SELECT
    lead.id AS `id`,
    lead.last_name
FROM
    `lead`
LEFT JOIN `account` AS `account1`
ON
    lead.account1_id = account1.id
WHERE
    lead.deleted = '0'
ORDER BY
    lead.created_at
DESC
[B]LIMIT 0, 20[/B]

Request 2:

SELECT
    lead.id AS `id`,
    lead.last_name
FROM
    `lead`
LEFT JOIN `account` AS `account1`
ON
    lead.account1_id = account1.id
WHERE
    lead.deleted = '0'
ORDER BY
    lead.created_at
DESC
[B]LIMIT 20, 20[/B]

If you take out the "LEFT JOIN `account` AS `account1` ON lead.account1_id = account1.id" part from the sql then everything works as expected - you get two different lists (in my case 20 and 12 unique records long, since the table lead has exactly 32 entries). But with this left join (which corresponds to n:1 link between lead and account tables) everything goes wrong and two result lists have some entries in common, so they are not unique (having some entries with equal data - it shouldn't happen if you load new entries by pressing "Show more...").

How this issue can be solved? Should I redefine the relationship between lead and account? Or maybe it is a bug within the n:1 relationship handling?

[viper]

Hi guys! Is anybody home? That's a real big and obvious issue, which exists for ages already. So I wonder why nobody noticed it. It's pretty easy to reproduce on the demo system:

You just go to https://demo2.espocrm.com/?lang=en_US#Lead and press "Show more (1)" at the page bottom in the hope to see this last hidden element (see attachments)... But the "Show more (1)" line just disappears and you see the same list. The missing element has been annihilated.

Obviously it has to do with the calculation of the offset for getting the new entries to appear on the list follow-on. In the SQL examples I mentioned in my previous post the difference between the requests should be "LIMIT 21, 20" or "LIMIT 19, 20" since it looks like the first element ("[0]") isn't properly counted. And it produces a real weird and an unexpected behaviour:

For example, click on the last entry in the list view ("Fransuah Riberie") and after switching to the detail mode click on the right arrow in the right upper conner (">" - this arrow actually should be disabled cause it's the last element on the list).. Then click one more time and more.. After a couple of clicks the list view for the leads will be shown in the activity feed. That's really weird. Hope this bug will be fixed soon.

[viper]

Couldn't attach this screenshot to my previous post, so it's presented in the new one.

[yuri]

> Is anybody home

It's Sunday today. We try not to work on weekends.

On the demo, it's an issue that leads ordered by the date-time column, that has the same value for all records. It's how mysql optimizer works (poorly). It depends on version and some params. We could solve it by adding 'id' column to ORDER BY .

[viper]

> It's Sunday today. We try not to work on weekends.

Thanks for the reply! There wasn't many activities in this forum section during the last two weeks that's why I was asking.

> We could solve it by adding 'id' column to ORDER BY.

Thanks a lot! Do I have to add an "ORDER BY" clause to some "Base.php" or could this issue also be solved by adding some options to a custom "Lead.json" (like changing the "collection" section)?

Btw, the issue is still there for other entities. For example, you can add some lines to Opportunities and play around with sorting order on the creation date column to get the same odd behaviour on follow-on entries. So it seems like a general solution for all of the entities should be developed.

[yuri]

If we will be adding extra order by ID, we will have a significant performance impact. Lists will load slower. I'm not sure how to solve this problem.

[yuri]

With this fix https://github.com/espocrm/espocrm/c...2847f221c6a49a it will be possible to specify

"orderById": true

in metadata > entityDefs > fields > (field you need to order by).

Then "id" will be added to order statement.

I also recommend to an add additional index with a pair of columns ["field", "id"].

[viper]

> With this fix https://github.com/espocrm/espocrm/c...2847f221c6a49a it will be possible

Thanks for the fast fix. Was it already applied on the demo system? If yes, it looks like that it only affects leads at the moment.

Hopefully a better solution with respect to the page load performance will be found until the next release. I tried two different hosting providers and this issue was present there too. So I suppose that a large number of users are affected by this.

[yuri]

It's not applied on the deme. Moreover it requires a manual change in metadata. https://github.com/espocrm/documenta...nt/metadata.md