Postgresql – Mealplan: Allow ordering until 9am

datetimepostgresql

Imagine you have a meal service and everyday there is a different meal which customers can order.

Mon, Dec 12: Pasta
Tue, Dec 13: Pizza
Wed, Dec 14: Gnocci

Orders are possible until 9am.

This mean Monday on 8am customers may order Pasta for today, at 9:15am they are no longer allowed to order for today.

How to handle this is with PostgreSQL.

I see two ways:

Create a cron job which switches the column "orderable" to False.
Create a view which calculates the field "orderable" on the fly.

Is there a feasible third or forth way?

Which way is simple and robust?

Best Answer

As long as you have the dates in a date column, you can use a simple CASE to choose, which meal is selected.

The result if the query, depends when you look for the example, it will choose pasta before 9 in the morning a Minute later and you would get gnocchi, you only have to enter the menus ahead of time

In an event you could delete the old menus, but that is not necessary, as you will not have millions of rows.

CREATe TABLe "OrDERS" ("orderdate" date, "food" varchar(40))

INSERT INTO "OrDERS" VALUES (CURRENT_DATE  ,'Pasta'), (CURRENT_DATE  + INTERVAL '1 day','Gnocchi')

SELECT to_char("orderdate",'Dy, Mon DD'), "food" FROM "OrDERS" 
WHERE "orderdate" = 
       CASE WHEN NOW() < now()::date + interval '9h' THEN now()::date
            ELSE now()::date + INTERVAL '1 day'
       END

to_char     | food  
:---------- | :-----
Tue, Dec 22 | Gnocci

db<>fiddle here

Related Solutions

PostgreSQL – Ordering Data

After the edit, the question is (at last!) clear. The problem is that the ordering you want is complicated. It's a "gaps-and-islands" problem. You want first to isolate "islands" with same location, i.e. consecutive (when ordered by tldid) rows that have same location and then reorder the rows in those islands based on the tldorder.

One way to solve this:

with trip_log_det_ordered as
  ( select 
        tldid, 
        tldaction, 
        tldactiondate, 
        locdescription,
        lttype, 
        tldorder, 
        case when locid <> lag(locid) over (order by tldid) then 1 else null end
            as location_change
    from trip_log 
        left join trip_log_det on tlid=tldtlid 
        left join locations on tldlocation=locid 
        left join location_types on loctype=ltid
    where tlid = 12 
  )
select 
    tldid, 
    tldaction, 
    to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, 
    locdescription,
    lttype, 
    tldorder
from 
    trip_log_det_ordered
order by 
    count(location_change) over (order by tldid
                                 rows between unbounded preceding
                                          and current row),
    tldorder ;

The else null and the rows between unbounded preceding and current row can be removed, as they are the default.

If you add the count(...) in the select list, you can see the islands numbering. First island will have 0 (location changes), second island will have 1, third will have 2, etc...

Further notes:

The query would be much, much simpler if the trip_log_det (tldactiondate) was populated and could be used for the ordering.

PostgreSQL Order By – How to Handle Ordering in Subqueries

You don't need a subquery at all. But you have to decide what ordering you want.

After the group by there is no tldid values any more. And every group will be made by collapsing many rows (with different tldid values) into one row. Which of them should be used for the ordering? Is it the maximum, the mimimum or some other?

You can write for example:

select locid, tlid, locdescription
from 
  trip_log left join trip_log_det on tlid = tldtripid 
  left join locations on tldlocation = locid
where tlid = 50 
group by locid, tlid, locdescription
order by min(tldid) ;

In Postgres, you could also use the non-standard DISTINCT ON syntax to achieve the same result. This is more flexible, as you can have the tldid and other (non-grouped) columns in the select list as well:

select distinct on (locid, tlid, locdescription)
    locid, tlid, locdescription
from 
  trip_log left join trip_log_det on tlid = tldtripid 
  left join locations on tldlocation = locid
where tlid = 50 
order by locid, tlid, locdescription,
         tldid asc ;

Best Answer

Related Solutions

PostgreSQL – Ordering Data

PostgreSQL Order By – How to Handle Ordering in Subqueries

Related Question