Getting List of Questions That Have Not Been Shown or Answered

Introduction

In this article, we will explore how to retrieve questions that have not been shown or answered. This involves joining two tables: Question and UserAnswerQuestion. We will also cover the SQL query required to achieve this.

Background

The problem at hand can be broken down into finding questions that a user has not answered yet. To do this, we need to join the Question table with the UserAnswerQuestion table based on the question_id field. The UserAnswerQuestion table contains records of answers given by users for specific questions.

The goal is to retrieve all questions that have no matching record in the UserAnswerQuestion table, indicating that they have not been answered yet.

What We’ve Tried

Before diving into the SQL query, let’s examine what we’ve tried so far. The code snippet below shows an attempt to get a list of questions that a user has not answered:

public List<Question> getAllQuestionsThatUserCanAnswer(String email, Long topic){
    List<Question> mList = this.questionRepository.findAllByTopicParentId(topic);
    if(mList==null) return null;
    List<UserAnswerQuestion> userAnswerQuestionList = this.userAnswerQuestionRepository.findAllByUserEmail(email);

    for (UserAnswerQuestion userAnswerQuestion : userAnswerQuestionList) {
        if(mList.contains(userAnswerQuestion.getQuestion())){
            if(userAnswerQuestion.getPassed()){
                mList.remove(userAnswerQuestion.getQuestion());
            }
        }
    }
    return mList;
}

However, this approach has its limitations. It only removes questions that have been answered correctly by the user and does not account for questions that have no matching record in the UserAnswerQuestion table.

SQL Query

The SQL query to retrieve all questions that a user has not answered is as follows:

select * from Question q 
left join Answer a on q.id = a.question_id 
where a.id is null OR (a.passed != 'true' AND a.user_id = 1)
order by a.passed;

Let’s break down the query:

• select * from Question q: This selects all columns (*) from the Question table.
• left join Answer a on q.id = a.question_id: This joins the Answer table with the Question table based on the question_id field. The left join keyword is used to specify that we want to keep all records from the left table (Question) and match them with records in the right table (Answer). If there are no matching records, the result set will contain NULL values for the columns in the right table.
• where a.id is null OR (a.passed != 'true' AND a.user_id = 1): This filters the results to include only questions that have not been answered. The conditions are:
  • a.id is null: Questions with no matching record in the Answer table.
  • (a.passed != 'true') AND (a.user_id = 1): Questions where the answer was not passed by the user.

Ordering

The results are ordered by the passed field to show questions that have not been answered first.

Conclusion

Retrieving questions that have not been shown or answered involves joining two tables and applying conditions to filter out unanswered questions. The SQL query provided in this article uses a left join to achieve this. By understanding how to structure the query, you can create an effective solution for your specific use case.

Example Use Case

Suppose we have a Question table with the following data:

And we also have an Answer table with the following data:

We can use the SQL query to retrieve all questions that have not been answered:

select * from Question q 
left join Answer a on q.id = a.question_id 
where a.id is null OR (a.passed != 'true' AND a.user_id = 1)
order by a.passed;

The result would be:

This shows us that the question “How does Git work?” has not been answered yet.

Last modified on 2024-02-15