On 01/04/2021 02:51, Glen Huang wrote: > Hi, > > I guess this question has been asked a million times, but all solutions I can find online don’t really work well for my case. I’ll list them here and hope someone can shed some light. > > My use case is to implement joining clubs that require entrance fee: > > 1. Each clubs only allows maximum number of members. > 2. When a person decides to join a club, the seat is reserved for a limited amount of time. If that person fails to pay within that period, the seat will be open again > > I want to write a query that can quickly list all clubs that still have open seats and #2 is where I want expiration to happen. > > The solutions I find so far: > > 1. Exclude closed clubs in queries and periodically delete expired members > > I can’t come up with a query that can accomplish this in an efficient way. > > WITH seated_member AS ( > SELECT > club_id, > count(member_id) AS num_seated_member > FROM member > WHERE paid OR join_time > now() - ‘1h’::interval > GROUP BY club_id > ), > open_member AS ( > SELECT > club_id, > max_num_member - coalesce(num_seated_member, 0) AS num_open_member > FROM club LEFT JOIN seated_member USING(club_id) > ) > SELECT club_id AS open_club > FROM open_member > WHERE num_open_member > 0 > > This requires going through all seated members, which can potentially be large and takes a long time. > > I can of course add an num_open_member column to the club table and index it, but the problem becomes how to automatically update it when a member expires, which take us back to square one. > > All following solutions assume I add this column and seek to find a way to update it automatically. > > 2. Run a cron job > > This won’t work because the number is updated only after the cron job is run, which only happens at intervals. > > 3. Update the column before running any related queries > > This requires I execute DELETE and UPDATE queries before all seat related queries. It’s hard to manage and seems to slow down all such queries. > > 4. pg_cron > > My environment wouldn’t allow me to install 3rd-party extensions, but even if I could, it seems pg_cron run cron jobs sequentially. I’m not sure it works well when I need to add a cron job for each newly joined member. > > — > > I’m not aware any other solutions. But the problem seems banal, and I believe it has been solved for a long time. Would really appreciate it if someone could at least point me in the right direction. > > Regards, > Glen > Possibly keep your count of members updated via a trigger? Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom ________________________________ Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to admin@xxxxxxxxxxxx<mailto:admin@xxxxxxxxxxxx> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
