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?

Answer 1

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

Answer 2

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:

1. 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.

2. The @HasMany annotation is redundant.

You can drop it for clarity.

3. 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();