Nested select is missing required properties when LEFT JOIN

Question

I have 4 database tables:

• Questionnaires
• Sections
• Questions
• Answers

I'm trying to get the questionnaire with nested values where Sections, Questions, and Answers are joined to each other. The query itself works but the problem is that the result only contains questions that has at least one answer.

The goal is to have a list of questions with all the answers - whether or not the answer is present.

Questionnaire
id	PrimaryKey
name	String

Sections
id	PrimaryKey
name	String
questionnaire_id	ForeignKey

Questions
id	PrimaryKey
text	String
section_id	ForeignKey

Answers
id	PrimaryKey
text	String
user_id	Number
question_id	ForeignKey

This is the query I'm trying to run:

@Injectable()
class QuestionnaireService {
  findOne(id: number, user_id: string) {
    return this.questionnaire
      .createQueryBuilder('questionnaire')
      .leftJoinAndSelect('questionnaire.sections', 'sections')
      .leftJoinAndSelect('sections.questions', 'questions')
      .leftJoinAndSelect('questions.answers', 'answers')
      .where('questionnaire.id = :id', { id })
      .andWhere('answers.user_id = :user_id', { user_id })
      .getOne();
  }
}

At the moment I have 14 questions in the database. And only 2 answers so the result is this:

Questionnaire {
  id: 1,
  name: 'Default DRI Questionnaire',
  sections: [
    Section {
      id: 1,
      name: 'Your security threat concerns',
      questions: [ [Question] ]
    },
    Section {
      id: 2,
      name: 'About your organization',
      questions: [ [Question] ]
    }
  ]
}

So, I'm expecting the 14 questions to be present despite the fact that most of them do not have answers at all.

In Sequelize I've done this with include property but in TypeORM couldn't find the way to do it. 😕

This is the query TypeORM is running:

SELECT "questionnaire"."id"                  AS "questionnaire_id",
       "questionnaire"."name"                AS "questionnaire_name",
       "sections"."id"                       AS "sections_id",
       "sections"."name"                     AS "sections_name",
       "sections"."questionnaire_id"         AS "sections_questionnaire_id",
       "questions"."id"                      AS "questions_id",
       "questions"."text"                    AS "questions_text",
       "questions"."section_id"              AS "questions_section_id",
       "answers"."id"                        AS "answers_id",
       "answers"."user_id"                   AS "answers_user_id",
       "answers"."question_id"               AS "answers_question_id",
       "answers"."questionnaire_id"          AS "answers_questionnaire_id",
FROM "questionnaires" "questionnaire"
         LEFT JOIN "sections" "sections" ON "sections"."questionnaire_id" = "questionnaire"."id"
         LEFT JOIN "questions" "questions" ON "questions"."section_id" = "sections"."id"
         LEFT JOIN "answers" "answers" ON "answers"."question_id" = "questions"."id"
WHERE "questionnaire"."id" = 1
  AND "answers"."user_id" = 1

Solution

Thanks to @Andrew

I had to use condition check alongside leftJoinAndSelect.

@Injectable()
class QuestionnaireService {
  findOne(id: number, user_id: string) {
    return this.questionnaire
      .createQueryBuilder('questionnaire')
      .leftJoinAndSelect('questionnaire.sections', 'sections')
      .leftJoinAndSelect('sections.questions', 'questions')
      .leftJoinAndSelect('questions.answers', 'answers', 'answers.user_id = :user_id', { user_id })
      .where('questionnaire.id = :id', { id })
      .getOne();
  }
}