Skip to content

Instantly share code, notes, and snippets.

@rotimi-best

rotimi-best/sql

Last active June 7, 2024 17:39
Show Gist options
  • Save rotimi-best/e001b54533a70df3358f7bfaa80c18fa to your computer and use it in GitHub Desktop.
Save rotimi-best/e001b54533a70df3358f7bfaa80c18fa to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. rotimi-best revised this gist Jun 7, 2024. 1 changed file with 35 additions and 13 deletions.
    48 changes: 35 additions & 13 deletions sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,5 @@
    drop function if exists "public"."get_explore_courses"(org_id_arg uuid, profile_id_arg uuid);

    drop function if exists public.get_explore_courses (uuid, uuid);

    create
    or replace function public.get_explore_courses (org_id_arg uuid, profile_id_arg uuid) returns table (
    @@ -13,19 +14,40 @@ or replace function public.get_explore_courses (org_id_arg uuid, profile_id_arg
    currency character varying,
    is_published boolean,
    total_lessons bigint,
    total_students bigint,
    progress_rate bigint,
    type "COURSE_TYPE",
    other_profile_id uuid
    type "COURSE_TYPE"
    ) language plpgsql as $function$
    BEGIN
    Return query
    select course.id, organization.id AS org_id, course.title, course.slug, course.description, course.logo, course.banner_image, course.cost, course.currency, course.is_published, (select COUNT(*) from lesson as l where l.course_id = course.id) AS total_lessons, (select COUNT(*) from groupmember as gm where gm.group_id = course.group_id AND gm.role_id = 3) as total_students, (select COUNT(*) from lesson_completion as lc join lesson as l on l.id = lc.lesson_id where l.course_id = course.id and lc.is_complete = true and lc.profile_id = profile_id_arg) AS progress_rate, course.type as type, (select groupmember.profile_id from groupmember where groupmember.group_id = "group".id and groupmember.profile_id != profile_id_arg limit 1) as other_profile_id
    from course
    join "group" on "group".id = course.group_id
    join organization on organization.id = "group".organization_id
    where course.status = 'ACTIVE' AND course.is_published = true AND organization.id = org_id_arg
    -- GROUP BY course.id, groupmember.profile_id
    ORDER BY course.created_at DESC;
    RETURN QUERY
    SELECT
    course.id,
    organization.id AS org_id,
    course.title,
    course.slug,
    course.description,
    course.logo,
    course.banner_image,
    course.cost,
    course.currency,
    course.is_published,
    (SELECT COUNT(*) FROM lesson AS l WHERE l.course_id = course.id) AS total_lessons,
    course.type AS type
    FROM
    course
    JOIN "group" ON "group".id = course.group_id
    JOIN organization ON organization.id = "group".organization_id
    WHERE
    course.status = 'ACTIVE'
    AND course.is_published = true
    AND organization.id = org_id_arg
    AND profile_id_arg NOT IN (
    SELECT
    groupmember.profile_id
    FROM
    groupmember
    WHERE
    groupmember.group_id = course.group_id
    )
    ORDER BY
    course.created_at DESC;
    END;
    $function$;
  2. rotimi-best created this gist Jun 7, 2024.
    31 changes: 31 additions & 0 deletions sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,31 @@
    drop function if exists "public"."get_explore_courses"(org_id_arg uuid, profile_id_arg uuid);

    create
    or replace function public.get_explore_courses (org_id_arg uuid, profile_id_arg uuid) returns table (
    id uuid,
    org_id uuid,
    title character varying,
    slug character varying,
    description character varying,
    logo text,
    banner_image text,
    cost bigint,
    currency character varying,
    is_published boolean,
    total_lessons bigint,
    total_students bigint,
    progress_rate bigint,
    type "COURSE_TYPE",
    other_profile_id uuid
    ) language plpgsql as $function$
    BEGIN
    Return query
    select course.id, organization.id AS org_id, course.title, course.slug, course.description, course.logo, course.banner_image, course.cost, course.currency, course.is_published, (select COUNT(*) from lesson as l where l.course_id = course.id) AS total_lessons, (select COUNT(*) from groupmember as gm where gm.group_id = course.group_id AND gm.role_id = 3) as total_students, (select COUNT(*) from lesson_completion as lc join lesson as l on l.id = lc.lesson_id where l.course_id = course.id and lc.is_complete = true and lc.profile_id = profile_id_arg) AS progress_rate, course.type as type, (select groupmember.profile_id from groupmember where groupmember.group_id = "group".id and groupmember.profile_id != profile_id_arg limit 1) as other_profile_id
    from course
    join "group" on "group".id = course.group_id
    join organization on organization.id = "group".organization_id
    where course.status = 'ACTIVE' AND course.is_published = true AND organization.id = org_id_arg
    -- GROUP BY course.id, groupmember.profile_id
    ORDER BY course.created_at DESC;
    END;
    $function$;