How to calculate a value into a own column without having to call the database for each record

Question

I am trying to load data to the grid from another database table.

My grid is created from User entity

private Grid<User> grid = new Grid<>(User.class, false);

User has several columns like

private Long userId;
private String username;
private String email;
.
.
.

The User contains the record identifier from the second table (entity) too:

private Long organizationId;

So I added columns to the grid:

grid.addColumn(User::getUserId).setAutoWidth(true).setHeader("id");
grid.addColumn(User::getUsername).setAutoWidth(true).setHeader("login");

And I created my "own" column which using data from another table (entity):

 grid.addColumn(user -> organizationService.findOrganizationNameByOrganizationId(user.getOrganizationId()).setAutoWidth(true).setHeader("organization name");

But problem is, that loading is very slow, because User table has about 500 000 rows and it send query to the database for each row ...

Is possible to load organization name by organization id defined in user entity in batch e.g. ?

Answer 1

First of all, I'd not bother with with enriching rows, in that way, if I don't have to. If the solution is as simple as just joining the data in the DB, creating a view, ... then just do that.

Yet, there are times, this has to be done efficiently, because the data to enrich does not come from the same source.

I think the best place to do that, is as part of the lazy loading of the data. Once you hold the stream in hand for the currently loaded page, you can map over the stream and enrich the data, or do an batch load etc. Pick what is most efficient.

If you have to do that a lot, make sure to provide useful tools inside your repositories; for quickly adding things just for one grid, you can as well highjack the DataProvider or it's successor.

Following an example (note the XXX):

@Route("")
class GridView extends Div {
    GridView() {
        add(new Grid<User>(User).tap {
            setItems({ q ->
                // XXX: this materializes the stream first to get all
                //      unique organizationId:s, batch-load the
                //      Organization, and finally transform the User
                def users = FakeUserRepo.page(q.offset, q.limit).toList()
                def orgaMap = FakeOrganizationRepo.batchLoad(users*.organizationId.toSet())
                users.stream().map {
                    it.organizationName = orgaMap[it.organizationId].name; it
                }
            }, { q ->
                FakeUserRepo.count()
            })
        })
    }
}

@TupleConstructor
class Organization {
    Integer id
    String name
}

class FakeOrganizationRepo {

    public static final Map<Integer, Organization> ORGANIZATIONS = (0..<5).collectEntries { [it, new Organization(it, "Organization $it")] }

    static Map<Integer, Organization> batchLoad(Set<Integer> ids) {
        ORGANIZATIONS.subMap(ids)
    }
}

@TupleConstructor
class User {
    Integer id
    String name
    Integer organizationId
    String organizationName
}

class FakeUserRepo {

    public static final Collection<User> USERS = (1..500_000).collect {
        new User(it, "User $it", it % FakeOrganizationRepo.ORGANIZATIONS.size())
    }

    static int count() {
        USERS.size()
    }

    static Stream<User> page(int offset, int limit) {
        USERS.stream().skip(offset).limit(limit)
    }

}