jooq single query with one to many relationship

Tags: , , ,



I have a table experiment and a table tags. There may be many tags for one experiment. schema:

--------                  --------
|Table1|  1           n   |Table2|
|      | <--------------> |      |
|      |                  |      |
--------                  --------
(experiment)              (tags)

Is it possible to create a query with jooq which returns the experiments and the corresponding List of tags?

something like Result<Record> where Record is a experimentRecord and a list of Tags, or a map<experimentRecord, List<TagRecord>.

I also have a query which returns only one result, is there something convenient out there?

EDIT: java8, newest jooq.

Answer

There are many ways to materialise a nested collection with SQL, and / or with jOOQ. I’m just going through some of them:

Using joins

If you don’t deeply nest those collections, denormalising (flattening) your results with a JOIN might do the trick for you, without adding too much overhead as data is being duplicated. Essentially, you’ll write:

Map<ExperimentRecord, Result<Record>> map =
DSL.using(configuration)
   .select()
   .from(EXPERIMENT)
   .join(TAGS)
   .on(...)
   .fetchGroups(EXPERIMENT);

The above map contains experiment records as keys, and nested collections containing all the tags as values.

Creating two queries

If you want to materialise a complex object graph, using joins might no longer be optimal. Instead, you probably want to collect the data in your client from two distinct queries:

Result<ExperimentRecord> experiments = 
DSL.using(configuration)
   .selectFrom(EXPERIMENT)
   .fetch();

And

Result<TagsRecord> tags =
DSL.using(configuration)
   .selectFrom(TAGS)
   .where(... restrict to the previous experiments ...)
   .fetch();
 

And now, merge the two results in your client’s memory, e.g.

experiments.stream()
           .map(e -> new ExperimentWithTags(
                e, 
                tags.stream()
                    .filter(t -> e.getId().equals(t.getExperimentId()))
                    .collect(Collectors.toList())
           ));

Nesting collections using SQL/XML or SQL/JSON

This question didn’t require it, but others may find this question in search for a way of nesting to-many relationships with jOOQ. I’ve provided an answer here. Starting with jOOQ 3.14, you can use your RDBMS’s SQL/XML or SQL/JSON capabilities, and then use Jackson, Gson, or JAXB to nest collections like this:

List<Experiment> experiments =
ctx.select(
      EXPERIMENT.asterisk(),
      field(
        select(jsonArrayAgg(jsonObject(TAGS.fields())))
        .from(TAGS)
        .where(TAGS.EXPERIMENT_ID.eq(EXPERIMENT.ID))
      ).as("tags")
    )
   .from(EXPERIMENT)
   .fetchInto(Experiment.class);

Where Experiment is a custom Java class like this:

class Experiment {
  long id;
  String name;
  List<Tag> tags;
}

class Tag {
  long id;
  String name;
}


Source: stackoverflow