SQL Joins not working correctly when no data present

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
    }

Answer

For anyone who stumbled on this question and is looking for an answer, you should use ON clause instead of WHERE, and also the ON clauses should be right after the LEFT JOINS:

"LEFT JOIN t.questions qa " +
"LEFT JOIN t.questions qd ON qd.persistDateTime < :now and :oneDay < qd.persistDateTime " + 
"LEFT JOIN t.questions qw ON qw.persistDateTime < :now and :oneWeek < qw.persistDateTime"

Advertisement

Answer