get_explore_courses.sql

sql

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 (
  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,
  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,
  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$;