CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Ben Smith
Ben Smith

Reputation: 861

Rails / SQL Query finding most recent Event

I'm using Rails 4.2 and PostgreSQL 9.4.

I have a basic users, reservations and events schema.

I'd like to return a list of users and the most recent event they attended, along with what date/time this was at.

I've created a query that returns the user and the time of the most recent event. However I need to return the events.id as well.

My application does not allow a user to reserve two events with the same start time, however I appreciate SQL does not know anything about this and thinks there can be multiple events in the result. Hence I am happy for the query to return an appropriate event ID at random in the case of a hypothetical 'tie' for events.starts_at.

User.all.joins(reservations: :event) .select('users.*, max(events.starts_at)') .where('reservations.state = ?', "attended") .where('events.company_id = ?', 1) .group('users.id')

The corresponding SQL query is: SELECT users.*, max(events.starts_at) FROM "users" INNER JOIN "reservations" ON "reservations"."user_id" = "users"."id" INNER JOIN "events" ON "events"."id" = "reservations"."event_id" WHERE (reservations.state = 'attended') AND (events.company_id = 1) GROUP BY users.id

The reservations table is very large so loading the entire set into Rails and processing it via Ruby code is undesirable. I'd like to perform the entire query in SQL if it is possible to do so.

My basic model:

User
has_many :reservations

Reservation
belongs_to :user
belongs_to :event

Event
belongs_to :company
has_many :reservations

Upvotes: 0

Views: 186

Answers (2)

Misunderstood
Misunderstood

Reputation: 5665

I think your query should focus first to retrieve the most recent reservation.

SELECT MAX(`events.starts_at`),`events"."id`,`user_id` FROM `reservations`  WHERE (reservations.state = 'attended')

Then JOIN the Users and Events.

Assuming the results will include every User and Event it may be more efficient to retrieve all users and events and store then in two arrays keyed by id.

The logic behind that is rather than a separate lookup into the user and events table for each resulting reservation by the db engine, it is more efficient to get them all in a single query.

SELECT * FROM Users' WHERE 1 ORDER BYuser_id`

SELECT * FROM Events' WHERE 1 ORDER BYevent_id`

I am not familiar with Rails syntax so cannot give exact code but can show using it in PHP code, the results are put into the array with a single line of code.

while ($row = mysql_fetch_array($results, MYSQL_NUM)){users[$row(user_id)] = $row;}

Then when processing the Reservations you get the user and event data from the arrays.

The Index for reservations is critical and may be worth profiling.

Possible profile choices may be to include and exclude 'attended' in the Index. The events.starts_at should be the first column in the index followed by user_id. But profiling the Index's column order should be profiled.

You may want to use a unique Index to enforce the no duplicate reservations times.

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

The generic sql that returns data for the most recent event looks like this:

select yourfields
from yourtables
join 
(select someField
, max(datetimefield) maxDateTime
from table1
where whatever
group by someField ) temp on table1.someField = temp.somefield
and table1.dateTimeField = maxDateTime
where whatever

The two "where whatever" things should be the same. All you have to do is adapt this construct into your app. You might consider putting the query into a stored procedure which you then call from your app.

Upvotes: 1

Related Questions