My problem is with the question_number field. I want this field to represent the question number in a particular quiz. For example, if there is a quiz with an id of 3 and it had two questions, I would expect the questions table to look like this:
id
question_number
title
options
correct_answer
quiz_id
1
1
What is a string
{“this”, true, 5}
“this”
1
2
1
What is 1+2?
{3, 5, 7}
7
3
3
2
What is an integer?
{5, 5.5, 1.2}
5
3
As seen from the table above, quiz with id 3 has two questions with the correct question numbers. However, how would I actually keep track of question_number? At first I thought I can just run a query.
For example, if quiz with id 3 has two questions and I wanted to add a third question. When adding a new question, I’d run a query that selects all rows in questions table that has a quiz_id of 3. In this case it would return 2 because there are two existing questions. Then I would simply do 2 + 1 giving me 3 which would make sense as it would be the third question.
The main problem is: What would happen if a question gets deleted from the quiz? How on earth would I update all question_number fields for all the questions in a quiz to be in the correct order. With my logic, if I deleted question number 1, the second question would still have a question_number of 2.
I’m starting to think I don’t have the right approach.
Are you using question_number as ordering questions in a quiz? If yes, then you can do something always rewrite the order before sending the data to web page or something.
Question number need not be continuous in database - For example 1 gets deleted - 3 is added, 4, is added and finally 3 gets deleted - if you query you will get 2, 4 rows - then you can rewrite serial no as 1, 2 for rows 2,4 before presenting or rendering.
Alternatively on delete - you can update all the rows in a transaction and store them in correct order. This will solve your problem.
I would do an Ecto.Multi, updating the questions numbers in the same transaction as deleting the question (since you’d only have to update the question_number of all questions having a higher value than the one who got deleted). Something like this (untested):
def delete_question(%Question{} = question) do
Ecto.Multi.new()
|> Ecto.Multi.delete(:deleted_question, question)
|> Ecto.Multi.update_all(:updated_questions, &do_update_questions_numbers(&1))
|> YourApp.Repo.transaction()
end
defp do_update_questions_numbers(%{deleted_question: deleted_question}) do
from(q in Question)
|> where([q], q.question_number > ^deleted_question.question_number)
|> update([], inc: [question_number: -1])
end