I've got the following code, where I build a tag from an entity
entityManager.createQuery("SELECT new com.myproject.rs.platform.models.dto.TagViewDto" +
"(t.id, t.name, t.description, count(DISTINCT qa.id) , count(DISTINCT qd.id), count(DISTINCT qw.id)) FROM Tag t " +
"LEFT JOIN t.questions qa LEFT JOIN t.questions qd LEFT JOIN t.questions qw " +
"WHERE qd.persistDateTime < :now and :oneDay < qd.persistDateTime " +
"and qw.persistDateTime < :now and :oneWeek < qw.persistDateTime " +
"GROUP BY t " +
"ORDER BY t.persistDateTime DESC", TagViewDto.class)
.setParameter("oneDay", localDateTime.minusHours(24))
.setParameter("now", localDateTime)
.setParameter("oneWeek", localDateTime.minusDays(7))
.setFirstResult((curPageNumber - 1) * itemsOnPage).setMaxResults(itemsOnPage)
.getResultList();
Each tag has an id, name, description and how many questions it has, how many it has in a day, and how many it has in a week.
The problem is, I can't seem to display tags that have no questions tied to them, and also if a tag doesn't have any daily or weekly question, the tag itself is also not displayed. I tried writing a coalesce() in the constructor itself, with the arguments of count and then 0, but it does not seem to work, and coalesce returns an int instead of a long.
How would I make tags with no questions or with no recent(in a day's or week's time) questions still get displayed? Preferably with these null values being replaced with a 0
EDIT: Here are some example datasets
tag:
- id: 1
name: "one"
description: "javascript"
persist_date: 2021-10-29 14:04:39
- id: 2
name: "two"
description: "java"
persist_date: 2017-03-12 00:00:00
- id: 3
name: "three"
description: "c"
persist_date: 2023-08-29 00:00:00
- id: 4
name: "four"
description: "cpp"
persist_date: 2020-21-29 00:00:00
- id: 5
name: "five"
description: "python"
persist_date: 2021-06-20 00:00:00
- id: 6
name: "six"
description: "go"
persist_date: 2021-04-29 00:00:00
question:
- id: 1 #current
title: "one"
description: "javascript"
persist_date: 2022-01-31 12:00:00
- id: 2 #current
title: "two"
description: "java"
persist_date: 2022-01-31 12:00:00
- id: 3 #current
title: "three"
description: "c"
persist_date: 2022-01-31 12:00:00
- id: 4 #threeDaysAgo
title: "four"
description: "cpp"
persist_date: 2022-01-28 12:00:00
- id: 5 #TwoDaysAgo
title: "five"
description: "python"
persist_date: 2022-01-29 12:00:00
- id: 6 #weekAndADayAgo
title: "six"
description: "go"
persist_date: 2022-01-23 12:00:00
questionHasTag:
- tag_id: 1
question_id: 6
- tag_id: 1
question_id: 5
- tag_id: 1
question_id: 4
- tag_id: 3
question_id: 6
- tag_id: 3
question_id: 2
- tag_id: 3
question_id: 1
- tag_id: 4
question_id: 6
- tag_id: 2
question_id: 2
- tag_id: 2
question_id: 6
and the expected tag json:
{
"id": 1,
"name": "one",
"description": "javascript ",
"questionCount": 3,
"questionCountOneDay": 0,
"questionCountOneWeek": 2
}
{
"id": 2,
"name": "two",
"description": "java ",
"questionCount": 2,
"questionCountOneDay": 1,
"questionCountOneWeek": 1
}
{
"id": 3,
"name": "three",
"description": "c ",
"questionCount": 3,
"questionCountOneDay": 2,
"questionCountOneWeek": 2
}
{
"id": 4,
"name": "four",
"description": "cpp ",
"questionCount": 1,
"questionCountOneDay": 0,
"questionCountOneWeek": 0
}
{
"id": 5,
"name": "five",
"description": "python ",
"questionCount": 0,
"questionCountOneDay": 0,
"questionCountOneWeek": 0
}
{
"id": 6,
"name": "six",
"description": "go ",
"questionCount": 0,
"questionCountOneDay": 0,
"questionCountOneWeek": 0
}