skip to Main Content

I am frontend engineer building something with supabase, I have written this postgres function

DROP FUNCTION IF EXISTS get_schedule_checklist_data;

CREATE OR REPLACE FUNCTION get_schedule_checklist_data(
    schedule_id UUID,
    blueprint_id_value UUID,
    event_type service_configuration_type
) 
RETURNS TABLE (
    title TEXT,
    schedule_id UUID,
    checklist_id UUID, 
    completed BOOLEAN,
    performed_by TEXT,
    performed_on_date TIMESTAMPTZ,
    parent_id uuid,
    order_number INT8,
    event_type TEXT,
    archived BOOLEAN
)
LANGUAGE sql
AS $function$
SELECT
    bc.title, 
    bcm.schedule_id,  -- This could be null
    bc.id AS checklist_id,
    bcm.completed,
    bcm.performed_by,
    bcm.performed_on_date,
    bc.parent_id,
    bc.order as order_number,
    bc.event_type,
    bc.archived
FROM
    blueprint_checklist bc
LEFT JOIN
    blueprint_schedule_completion_mapping bcm 
ON
    bcm.schedule_id = schedule_id AND bc.id = bcm.blueprint_checklist_id
WHERE
    bc.blueprint_id = blueprint_id_value
    AND bc.event_type = event_type;
$function$;

in this, for bcm.schedule_id = schedule_id AND bc.id = bcm.blueprint_checklist_id condition, I only want the record with most recent performed_on_date in table.

How can I do it?

2

Answers


  1. I think, if do not consider performance, you can get most resent row (rows)

    select *
    from(
    SELECT
        bc.title, 
        bcm.schedule_id,  -- This could be null
        bc.id AS checklist_id,
        bcm.completed,
        bcm.performed_by,
        bcm.performed_on_date,
        rank()over(partition bcm.schedule_id, bcm.blueprint_checklist_id
                    order by bcm.performed_on_date desc) as rank
        bc.parent_id,
        bc.order as order_number,
        bc.event_type,
        bc.archived
    FROM
        blueprint_checklist bc
    LEFT JOIN
        blueprint_schedule_completion_mapping bcm 
    ON
        bcm.schedule_id = schedule_id AND bc.id = bcm.blueprint_checklist_id
    WHERE
        bc.blueprint_id = blueprint_id_value
        AND bc.event_type = event_type
    )t
    where rnk=1;
    
    Login or Signup to reply.
  2. Using DISTINCT ON (bc.id, bcm.schedule_id) and ordering by bc.id, bcm.schedule_id, bcm.performed_on_date will return the desired results. The following also addresses ambiguous references in the original code caused by unqualified identifiers:

    CREATE OR REPLACE FUNCTION get_schedule_checklist_data(
        schedule_id UUID,
        blueprint_id_value UUID,
        event_type service_configuration_type
    ) 
    RETURNS TABLE (
        title TEXT,
        schedule_id UUID,
        checklist_id UUID, 
        completed BOOLEAN,
        performed_by TEXT,
        performed_on_date TIMESTAMPTZ,
        parent_id uuid,
        order_number INT8,
        event_type TEXT,
        archived BOOLEAN
    )
    LANGUAGE sql
    AS $function$
    SELECT DISTINCT ON (bc.id, bcm.schedule_id)
        bc.title, 
        bcm.schedule_id,  -- This could be null
        bc.id AS checklist_id,
        bcm.completed,
        bcm.performed_by,
        bcm.performed_on_date,
        bc.parent_id,
        bc.order as order_number,
        bc.event_type,
        bc.archived
    FROM
        blueprint_checklist bc
    LEFT JOIN
        blueprint_schedule_completion_mapping bcm 
    ON
        bcm.schedule_id = get_schedule_checklist_data.schedule_id AND bc.id = bcm.blueprint_checklist_id
    WHERE
        bc.blueprint_id = get_schedule_checklist_data.blueprint_id_value
        AND bc.event_type = get_schedule_checklist_data.event_type
    ORDER BY bc.id, bcm.schedule_id, bcm.performed_on_date DESC;
    $function$;
    

    If the result of this function is to be joined to other tables, then consider implementing it as a view to take advantage of the query engine’s optimization features. Functions act as optimization fences.

    CREATE OR REPLACE VIEW current_schedule_checklist_data
    SELECT DISTINCT ON (bc.id, bcm.schedule_id)
        bc.title, 
        bcm.schedule_id,
        bc.id AS checklist_id,
        bcm.completed,
        bcm.performed_by,
        bcm.performed_on_date,
        bc.parent_id,
        bc.order as order_number,
        bc.event_type,
        bc.archived
    FROM
        blueprint_checklist bc
    LEFT JOIN
        blueprint_schedule_completion_mapping bcm 
    ON
        bc.id = bcm.blueprint_checklist_id
    ORDER BY bc.id, bcm.schedule_id, bcm.performed_on_date DESC;
    

    (I’ve not yet tested this code since the original post lacked DDL, sample data, and expected output and I haven’t had time to create my own test cases.)

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search