resource.js

// @flow
import { isFuture, subSeconds } from 'date-fns';
import { sortBy, uniq } from 'lodash';
import Sequelize from 'sequelize';
import sequelize from '../../../db/sequelize';

import { Input, Output } from '../../../api';
import { Course } from '../../../models';

const { Op } = Sequelize;

export default async function listAvailableCourse(input: Input, output: Output): Promise<void> {
  const { query } = input.req;
  const searchIndex = ['name'];
  const validFilters = [
    'credittype',
    'categoryId',
    'fieldOfStudyId',
    'id',
  ];
  const webinarInclude = {
    model: Course.getModel('Webinar'),
    include: [{ model: Course.getModel('WebinarRegistration') }],
  };
  const include = [
    { model: Course.getModel('SelfStudyVideo') },
    webinarInclude,
  ];
  let attributes = [];

  // Only return published courses, or archived courses with webinars scheduled in the future.
  const { user: userId } = input.jwt;
  const [ wCourseIds ] = await sequelize.db.query(
    `SELECT DISTINCT(course_id) FROM webinars_registrations JOIN webinars ON webinars_registrations.webinar_id = webinars.id WHERE user_id = ${userId};`
  );
  const [ ssCourseIds ] = await sequelize.db.query(
    `SELECT DISTINCT(course_id) FROM self_study_registrations WHERE user_id = ${userId};`
  );
  const takenCourseIds = uniq([ ...wCourseIds, ...ssCourseIds ].map(x => x.course_id));
  Object.assign(query, {
    where: {
      // $FlowFixMe
      id: { [Op.not]: takenCourseIds },
      // $FlowFixMe
      [Op.or]: [
        { status: 'published' },
        Sequelize.literal('"Course"."status" = \'archived\' and (SELECT COUNT(*) FROM webinars WHERE webinars.scheduled_on > now() and webinars.course_id = "Course".id) > 0'),
      ],
    },
  });

  if (query.order === 'popular') {
    attributes.push([
      Sequelize.literal('(SELECT COUNT(*) FROM webinars_registrations JOIN webinars ON webinars.id = webinars_registrations.webinar_id WHERE webinars.course_id = "Course".id) + (SELECT COUNT(*) FROM self_study_registrations WHERE self_study_registrations.course_id = "Course".id)'),
      'registrations',
    ]);
    query.order = [[Sequelize.literal('"registrations"'), 'DESC']];
  } else if (query.order === 'topRated') {
    attributes.push([
      Sequelize.literal('(SELECT COALESCE(AVG(rating), 0) FROM courses_ratings WHERE courses_ratings.course_id = "Course".id)'),
      'averageRating',
    ]);
    query.order = [[Sequelize.literal('"averageRating"'), 'DESC']];
  } else if (query.order === 'scheduledOn') {
    // when sorting by scheduled on, limit the results to webinars scheduled before cut off time.
    const webinarCutOff = subSeconds(new Date(), 600);
    // $FlowFixMe
    webinarInclude.where = { scheduledOn: { [Op.gte]: webinarCutOff } };

    query.order = [
      [{ model: Course.getModel('Webinar') }, 'scheduledOn', query.direction || 'asc'],
    ];
  }

  const { count, rows } = await Course.search(
    query,
    include,
    validFilters,
    searchIndex,
    attributes,
  );

  const response = rows.map((course: Object) => {
    const {
      SelfStudyVideos,
      Webinars,
      ...entity
    } = course.toJSON();


    return {
      ...entity,
      selfStudyVideos: sortBy(SelfStudyVideos, 'position'),
      webinarScheduled: sortBy(
        Webinars.filter(webinar => isFuture(webinar.scheduledOn)),
        'scheduledOn',
      ),
    };
  });

  const headers = { 'X-Count': count };
  output.setResponse(response, headers);
}