CODEWITHSUNDEEP
javajpaquerydsl
Mark Buikema
Mark Buikema

Reputation: 2540

How to get total of sizes of nested lists of an entity in a query?

I am using JPA and I have a data structure like this:

class ContainerA {
  long id;
  List<ContainerB> bs;
}

class ContainerB {
  long id;
  List<ContainerC> cs;
}

class ContainerC {
  long id;
  List<Device> devices;
}

class Device {
  long id;
  List<Attribute> attributes;
}

class Attribute {
  long id;
  long value;
}

What I need is a query to get a list of all ContainerA id's, with their corresponding total number of devices, total number of attributes, and the sum of all values;

I am new to JPA and I tried using QueryDSL but I did not manage to get it to work.

What query is the best way to get the information I need?

Upvotes: 2

Views: 599

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221106

Use SQL for this kind of report. It's easier and I'm taking bets it'll be much faster, too. Here's a really simple query:

SELECT 
  a.id,
  COUNT(DISTINCT d.id) AS Devices,
  COUNT(att.id) AS Attributes,
  SUM(att.value) AS TotalAttributeValue
FROM ContainerA AS a
LEFT JOIN ContainerB AS b ON a.id = b.a_id
LEFT JOIN ContainerC AS c ON b.id = c.b_id
LEFT JOIN Device AS d ON c.id = d.c_id
LEFT JOIN Attribute AS att ON d.id = att.d_id
GROUP BY a.id

I'm assuming that there are only one-to-many relationships in your schema. If there's a many-to-many relationship between devices and attributes, the query might be a bit more complicated.

Upvotes: 10

Related Questions