Unable to Eager load children using getAll(..)

Question

We are trying to query Child records eagerly by defining @HasMany relationship and by using include() as mentioned here: Lazy and Eager

Table Structure:

Parent(id, name);
Child(id, name, parent_id);

Parent Class:

@HasMany(foreignKeyName = "parent_id", child = Child.class)
public class Parent extends Model {
}

Child class:

public class Child extends Model {
}

Test Client making a query:

           LazyList<Parent> parents = Parent.find("id in (115, 78)").include(Child.class);
           for (Parent parent : parents) {
                int id = parent.getInteger("id");
                String name = parent.getString("name");
                System.out.println("ID:" + id);
                System.out.println("Name:" + id);

                LazyList<Child> children = parent.getAll(Child.class);
                System.out.println("children: " + children.size());

           }

The output from the above after enabling logging shows:

[main] INFO org.javalite.activejdbc.ModelFinder - Loading models from: ./classes/activejdbc_models.properties
[main] INFO org.javalite.activejdbc.Registry - Registered model: class org.example.Parent
[main] INFO org.javalite.activejdbc.Registry - Registered model: class org.example.Child
[main] INFO org.javalite.activejdbc.Registry - Fetched metadata for table: parent
[main] INFO org.javalite.activejdbc.Registry - Fetched metadata for table: child
[main] INFO org.javalite.activejdbc.MetaModel - Association found: Parent  ----------<  Child, type: has-many
[main] INFO org.javalite.activejdbc.MetaModel - Association found: Child  >----------  Parent, type: belongs-to

[main] INFO org.javalite.activejdbc.LazyList - {"sql":"SELECT * FROM parent WHERE id in (115, 78)","params":[],"duration_millis":300,"cache":"miss"}
[main] INFO org.javalite.activejdbc.LazyList - {"sql":"SELECT * FROM child WHERE parent_id IN (?, ?) ORDER BY id","params":[115,78],"duration_millis":291,"cache":"miss"}

ID: 115
Name: Junk1

Children: 0 <-- we have 2 for 115

ID: 78
Name: Junk2

Children: 0 <-- we have 3 for 78

If we change the Parent query like the following, it will load the children but makes extra calls to the DB.

LazyList<Parent> parents = Parent.find("id in (115, 78)").**load()**.include(Child.class);

What are we doing wrong and is it possible to eagerly load children using ActiveJDBC?

I wrote a test that replicates your code, but it works as expected, printing the correct information. The last snippet of code in your question is hard to understand. Maybe this is a formatting issue? Can you please fix this: `LazyList parents = Parent.find("id in (115, 78)").**load()**.include(Child.class);` so we had a better idea what the issue is? — ipolevoy, Jun 12 '23 at 01:47
In addition, can you please provide the JavaLite and Java versions? — ipolevoy, Jun 12 '23 at 01:50
Thanks @ipolevoy for your quick response. That line with **load()** is to highlight the load() call in bold. However, it just emitted it straight with it. That call is executed as LazyList parents = Parent.find("id in (115,78)").load().include(Child.class);. Also, the javalite versions I tried are 2.6-j8 with JDK8 and 3.4-j11 with JDK11. Thanks. — Pratap R, Jun 12 '23 at 12:15

score 2 · Accepted Answer · answered Jun 14 '23 at 01:15

Thanks @ipolevoy for all your help.

Figured out the issue. The foreignKey field is 'integer' in Parent and is 'bigint' in Child in the DB. This is causing the child records to not load with 'getAll' even though the queries are generated correctly. The db seems to allow this mis-match.

If possible please add this as a note in the documentation so that a simple oversight like this can be rectified easily. Thanks again for all your help and the framework is the best and by far the easiest one to use and debug. Best regards

Can confirm that this causes issues - I had a simialr problem and ensuring that the PK of the parent and FK of the child are the same was the solution. — Dominique M, Jul 31 '23 at 15:13

score 0 · Answer 2 · answered Jun 12 '23 at 16:50

Since ActiveJDBC is lazy by default, by calling the include(..) method, you merely are configuring your request, but not calling it yet.

For instance, this code:

LazyList<Parent> parents = Parent.find("id in (115, 78)").include(Child.class);

will not make a call to the database until you try to use the parents object. This is the reason why we call it lazy.

However, the method load() will force the immediate call to the database.

This means that in your example you load data from DB before completing the configuration of your request. In other words:

LazyList<Parent> parents = Parent.find("id in (115, 78)").load().include(Child.class);

The load() is called before include(..). The include() is useless because the load() already loaded data with a default configuration.

What you need is to reverse the order:

LazyList<Parent> parents = Parent.find("id in (115, 78)").include(Child.class).load();

This way, the system will generate a correct number of requests.

Other thoughts:

Do not casually use the load() method. A better solution is:

LazyList<Parent> parents = Parent.find("id in (115, 78)").include(Child.class);

The system will load the data at the time you actually access it.

The @HasMany annotation is redundant.

You can drop it for clarity.

The ID type is not always an Integer

This code is not always optimal:

int id = parent.getInteger("id");

This is because you might run into issues if your underlying database stores IDs as something else. Try to treat them as opaque objects:

Object id = parent.getId();

Thanks @ipolevoy for your response and some great insights into it. However, as I mentioned - that line with the load() was only to say that it is working but in a different way. We however want to use include() the way it is intended to in the javalite/activeJDBC documentation and not use load() as it is not optimal for our Production use to load the data upfront. The main reason to test with load() is the 'lazy' call is not returning any data from the child table. After your suggestion, reversing the call to the load() as suggested by you is still not yielding child records. — Pratap R, Jun 12 '23 at 22:45
@PratapR, I developed a new example app that demonstrates the use of the `include()` with exactly your case, but was unable to reproduce the issue. Please, see https://github.com/javalite/activejdbc-include. Please, see if you can reproduce this problem. — ipolevoy, Jun 13 '23 at 22:56
Thanks @ipolevoy for the example app. I'll try it right away and will let you know how it goes. I'm using Postgres and that is the only difference I see in the code you shared vs what I have. — Pratap R, Jun 13 '23 at 23:19

Unable to Eager load children using getAll(..)

2 Answers2