mysql - Efficient way of counting number of records per day within date range in Rails without requiring one query per day -


rails app using fullcalendar.js generate reservation calendar shows, on each day, count of check-ins, check-outs , in progress reservations for each date.

this sample outcome:

enter image description here

using sql i'm able group reservations check-in , check-out , generate json count each day (since these 2 dates - check in , check-out - actual columns in database). takes 2 queries (one check-ins, 1 check-outs).

but don't know how handle in progress counts, since dates not in database, being void between check_in , check_out dates.

my current solution (the 1 think of) requires looping on each day of calendar , issuing sql count reservations check_in < day , check_out > day, takes lot of sql queries (one each day in calendar view). current working code:

# start_date , end_date provided fullcallendar.js params start_date, end_date = date.parse(start_date_string), date.parse(end_date_string)  # in progress last_in_progress_event = nil  array = []  (start_date..end_date).each |day|    count = current_user.reservations.where("date(check_in) < :date , date(check_out) > :date", date: day).size    if count > 0     # if count > 0, there's active reservation on day. if     # count changed when compared previous day, create new event     # on fullcalendar (to change `title` of event new count).     # if count remained same previous event, can update     # it's end date current date being analyzed se nice ui     # (continuous event while count doesn't change instead of 1 event     # per day)      if last_in_progress_event && last_in_progress_event[:count] == count       # day + 1 required due fullcalendar.js cutting event short 1 day       last_in_progress_event[:end] = (day + 1)     else       last_in_progress_event = {         title: "#{count} in progress",         start: day,         end: (day + 1),         allday: true,         classname: 'bgm-lightblue',         count: count       }        array.push(last_in_progress_event)     end    end # if count > 0  end  array

would there way of building hash of {day: in_progress_count} without 30+ queries per month?

could try one?

create procedure `getinprogresscount`(m_datefrom date,                                        m_dateto date) begin      declare m_counter integer;     declare m_sqldates longtext;     declare m_checkdate date;     set m_counter = 0;     set m_sqldates = '';      createdates: loop       set m_checkdate = date_add(m_datefrom, interval m_counter day);       set m_sqldates = concat(m_sqldates, if(trim(m_sqldates) <> "", " union ",""),"(select '", m_checkdate,                               "' checkdate )");       set m_counter = m_counter + 1;       if m_counter < datediff(m_dateto, m_datefrom)+1           iterate createdates;       else           leave createdates;       end if;      end loop createdates;     set @m_sqlinprogress = concat("select v.checkdate, count(*) inprogresscount (",m_sqldates,") v ",                                   "left join yourtable t on t.check_in < v.checkdate , ",                                   "t.check_out > v.checkdate group v.checkdate");       prepare m_sqldates @m_sqlinprogress;     execute m_sqldates;     deallocate prepare m_sqldates; end

modify code based on requirements. play until desired output.


Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more