I've been reading the association documentation for Sequel for some time
now. I'm trying to work out how to access an immutable data structure I
have for a process in our application. This is the scenario.
To maintain an immutable history of the lifecycle of a user, I have two
main tables which are joined by a foreign key. Overtime, rows are added to
the user_stages table as the user progresses from states like 'pending' to '
accepted', and on to (say) 'verified', or 'rejected', etc. The
relationship is as follows:
# Database schema:
# users user_stages
# :id <----\ :id
# :auth_code \----- :user_id
# :status
The class structure is as follows:
class User
one_to_many :user_stage
one_to_one :user_stage
end
class UserStage
many_to_one :user
end
For the sake of this example I'm relying on the primary keys being
monotonically increasing over time with each insert. Thus, the highest
value for :id gives the most recent row.
I have two associations on User because I may want to get the history of
all changes.
My focus here is on the limiting the one_to_one association to retrieve the
latest row for the UserStage entity for a given User since this will give
me the current status of the user.
The SQL to retrieve this current User tuples can be written as follows:
select u.*, us.stage from users u
inner join user_stage us on us.user_id = u.id and
us.id = (select max(us2.id) from user_stage us2
where us2.user_id = u.id)
I've struggled for a number of days trying various queries to get what I
want. I'm curious to know whether this is something that can be defined in
the model, or is it something that needs to be maintained in the queries?
Some things I've worked out/tried include:
To find the most recent UserStage :id for the User with :id equal to 13:
UserStage.select(Sequel.function(:max, :id)).where(:user_id, 13)
Failed attempt to get a tuple for a single User , with :id equal to 13:
User.where(Sequel.qualify(:users, :id) => 13)
.association_join(:user_stage)
.select_all(:user)
.select_append(Sequel.qualify(:user_stage, :status))
.where(Sequel.qualify(:user_stage, :id) =>
UserStage.select(Sequel.function(:max, :id))
.where(Sequel.qualify(:user_stage, :id) => 13)
This doesn't work. I've looked at the SQL.
I think I have a fundamental misunderstanding of the model/query
relationship with ruby/Sequel.
Any recommendations gratefully accepted.
Cheers, S t u a r t .
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/4199c84a-a849-4191-a975-dac3d7e7fc04n%40googlegroups.com.
