Active Record Rails query

0
votes

Could someone please help me on Rails query regarding this

I have mysql table like this. There is a recreation club. ticket_id is unique. There is entry and exit for each ticket (member).

I want to identify on particular time, how many members are inside. suppose if i want to know the inside members by the time '2015-12-17 16:48:00'.

id    ticket_id  event_type  timestamp  

1739    869 entry   2015-12-17 15:09:51 

1740    869 entry   2015-12-17 17:46:55 

1730    864 exit    2015-12-17 16:48:27
2
mysqlruby-on-railsactiverecord

2 Answers

1
votes

Assumption 1 : Every ticket_id can have multiple entries and exits. The data you have provide has 2 entries for ticket_id 869. Hence, the ActiveQuery results are subject to uniq. Even when a person has multiple entries (or exits) before required time, they are counted only once.

Assumption 2 : In some cases, between 2 entries, an exit may not be captured, as demonstrated by the sample data. Some discrepancy in the result might creep in because of this.

You can use the following query to obtain all people who are inside at one particular point of time, say reqd_timestamp :

@entered = Member.where("timestamp <= ? AND event_type = ?", 
                        reqd_timestamp, 'entry')
                 .pluck(:ticket_id).uniq 
@exited = Member.where("timestamp <= ? AND event_type = ?", 
                       reqd_timestamp, 'exit')
                .pluck(:ticket_id).uniq
@inside = (@entered - @exit).count

Please note, ticket_id 869 will be counted as being inside as of 2015-12-17 16:48:00 because he has an entry with id 1739 prior to the timestamp. He actually may have exited before stipulated time and subsequently making another entry as id 1740. But no data being available, that particular information cannot be captured.

0
votes

Try this

@members = Member.where("date(timestamp) =? AND event_type=?", '2015-12-17 16:48:00', 'entry').count