Crud: [4.1] Roles with thousands of users throw error on search

[selected-pixel-jameson]

Bug report

What I did

Implemented the Roles Controller

What I expected to happen

A list of Roles to be displayed

What happened

An error was displayed and the list of roles is not returned.

What I've already tried to fix it

If I search for a specific role the role will come back successfully as long as it has a small number of users attached to it. I've found that the issue is related to this controller not taking into consideration that people may have a large number of users.

If I search for a role that has a minimal amount of users then the role will come back correctly. If I search for the role that is attached to all 300k of my users this throws an error. This happens on my local server as well as my production servers. It happens very quickly so it's not timing out.

This causes the initial listing of roles to fail to load and is basically broken.

Backpack, Laravel, PHP, DB version

PHP VERSION:

PHP 7.3.15-3+ubuntu18.04.1+deb.sury.org+1 (cli) (built: Feb 23 2020 07:23:33) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.15, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.3.15-3+ubuntu18.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

LARAVEL VERSION:

v7.28.3@b0942c391975972b1a54b2dc983e33a239f169a9

BACKPACK VERSION:

4.1.25@23fb1ed5153d5465d49d2fe1ca265a5985c78877

[pxpm]

Hello @selected-pixel-jameson

If timeout is not the culprit I wonder what is the error displayed?

Best,
Pedro

[selected-pixel-jameson]

I have set the timeout on my server to 180 seconds. The response still errors after 15 seconds. There is no log filed within the Laravel log and no response is returned from the server in the http request.

I just get a 500 internal error in the Chrome Console pointing tot he app.js file.

This is a huge piece of functionality for this platform and it's pretty frustrating that it's more or less unusable.

[tabacitu]

Thanks @selected-pixel-jameson it does paint a better picture.

In regards to app.js - I have no idea what that is, it's definitely not part of Backpack. The default Backpack JS file is bundle.js, so it sounds like you have a custom app.js file that you're loading, and you have a JS error there. Or maybe that the JS is taking so long to load that it commits suicide, that's been known to happen 🙄 Such a quitter, javascript 🤣

---

That being said, I have very good news. I've investigated the issue, and it does seem like using the relationship_count column is not optimal in this use case. Using the relationship_count column on a related table with thousands or millions of entries is bound to have slow performance, because it will load those related models on page.

There's an easy way to go around this - instead of using the relationship_count column, you can:

• use Laravel's withCount('users') method to append a fake column to the Eloquent result, without loading the users;
• use text column to show users_count instead of using the relationship_count column

This will reduce the number of loaded Users to... zero. Here's a before&after, you'll notice there's just one User loaded, that's the logged in user:

It's not clear to me whether you've installed our PermissionManager add-on or you're doing something very similar. In any case:

• if you are using PermissionManager, I've moved this issue there and submitted PR https://github.com/Laravel-Backpack/PermissionManager/pull/256 that does exactly what I mentioned above; I'll merge it right away so a composer update should do it for you;
• if you are NOT using PermissionManager, you can follow the instructions above (or the code example in the PR) to achieve the same result;

Let me know if this fixes it for you, I'm pretty sure it will. Thanks for letting us know about this issue - we had no idea relationship_column was this inefficient with big tables.

Cheers!

[tabacitu]

Added a note to the relationship_count docs to make this crystal-clear - https://github.com/Laravel-Backpack/docs/commit/927537aa6758ab0182b40215e7b844ba1aea616f

Now I'm wondering if we should have this column at all, since the text column can do it better. Unfortunately I see no way to change relationship_column to be more performant.

[selected-pixel-jameson]

Issue is resolved. Thank you.