How to query this in Rails?

Question

I have a model submission and it has_many submissionstate (and two tables submissions and submissionstates respectively).

submissionstates has a foreign key submission_id and another state_id column (and updated_at, id)

Every time if there is state change for submission, a new entry with the new state will be created in the submissionstates table.

I want to show entries that has state_id = 0 and the state is the latest state of the submission.

For example, a submission has two states (A(state_id = 0) and B (state_id = 1)), B is added after A. So I do not want this submission to be shown on the page since even though it has A, it is not the latest state (which is B).

I used:

@submissions = Submission.includes(:submissionstates).where("submissionstates.state_id = 0").all

But the result will includes all the state with state_id = 0 even if they are not the latest state of a submission.

What should I change in the query in order to make this work?

Answer 1

This doesn't really answer your question but it solves your problem

You say

For example, a submission has two states (A(state_id = 0) and B (state_id = 1)), B

Which is just wrong. State implies singularity. i.e. a kettle can not be both boiling and cold. Consider moving your state flag into the submissions table. If you need to know when states changed then keep your submissionstates table but you would be using this for a totally different purpose.

by doing this you can then use

@submissions = Submission.where("state" = 0)

You could then set up scopes for each state if you wished e.g.

scope :state_0, where(:state => 0) Which leaves you with

@submissions = Submission.state_0

Which is far more scale-able than your approach By keeping the submissionstates table you can still list the dates and times of changes to states for a particular submission by making use of the created_at field but this becomes more of an auditing trail than for normal live usage

UPDATE based on comments

Using a separate submissionstates table is fine as it will give you the audit trail you need but you should still hold the current state on the submission table.

Updating the submission record every time a state changes is simple enough to achieve in a and performance wise it's no big deal especially when compared to the performance issue you WILL face if trying to determine the current state from the submissionstates table.

I'm saying that you need both. If there is an issue you have with keeping the current state on the submissions table then please shout and I'll see if I can address that

Answer 2

You have a problem with your Database structure!

You have to remember one thing ALWAYS when it comes to relational databases: There's no general or automatic ordering of the rows!

What I mean: You can't rely on the order of an query's output. The problem is: The DBMS optimizes your query, and if you don't call ORDER BY it is not defined in which order it will return the rows. (Even if it looks like it's always the same order, it could change on upgrading the DBMS or simply because a sack of rice fell over in China). DON'T RELY ON THAT!

So there's only one logical solution: A third column for your submissionstate table, either an auto-generated primary key or a timestamp. If you won't ever need the time of creation, go with the primary key.

Then your table would look like:

id | submission_id | state_id
-----------------------------
1  | 4             | 3
2  | 8             | 3
3  | 4             | 2
4  | 6             | 1
5  | 4             | 5
6  | 8             | 0

If I understood you correctly, you want those results:

• If you look for submission_id = 4 you want to receive state_id = 5
• If you look for submission_id = 8 you want to receive state_id = 0
• If you look for submission_id = 6 you want to receive state_id = 1

Right?

---

Solution(s):

So, when an id field is given, the query you're looking for is:

SELECT submission_id, state_id
FROM submissionstates ss
JOIN (
  SELECT MAX(id) as id
  FROM submissionstates ss
  GROUP BY submission_id
) x
ON ss.id = x.id

If you only want the rows where the current state_id = 0, then:

SELECT submission_id, state_id
FROM submissionstates ss
JOIN (
  SELECT MAX(id) as id
  FROM submissionstates ss
  GROUP BY submission_id
) x
ON ss.id = x.id
WHERE state_id = 0

If you already have a created_at column, then use it instead of the id column:

SELECT submission_id, state_id
FROM submissionstates ss
JOIN (
  SELECT MAX(created_at) as created_at
  FROM submissionstates ss
  GROUP BY submission_id
) x
ON ss.created_at = x.created_at
WHERE state_id = 0