How to record the number of a particular row in a table?

The title doesn’t explain what I am trying to achieve very well, so here is some context:

I have a quiz table with the following fields:

id quiz_name

I then have an associated questions table with the fields:

id question_number title options correct_answer quiz_id

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.

You could also create a Quiz schema like this:

defmodule Quiz do
  use Ecto.Schema
  schema "quizzes" do
    has_many :questions, Question, preload_order: [asc: :question_number], on_replace: :delete

  def changeset(quiz, params) do
    |> cast(params, [])
    |> cast_assoc(:questions)
    |> update_question_nos()

  defp update_question_nos(changeset) do
    change =
      |> get_change(:questions)
      |> Enum.with_index(1)
      |> {change, index} ->
        put_change(change, :question_number, index)

    |> put_change(:questions, change)

Then the question_no would be updated based on the position in the list.

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.delete(:deleted_question, question)
  |> Ecto.Multi.update_all(:updated_questions, &do_update_questions_numbers(&1))
  |> YourApp.Repo.transaction()

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])