• Vamos começar criando um método no serviço que irá ter uma query personalizada para obter uma lista dos 10 cursos com a maior contagem de likes. Adicione o código do método:

// src/services/courseService.ts

import { Op } from 'sequelize'
import { Course } from '../models'

export const courseService = {

// ...

  getTopTenByLikes: async () => {
    const results = await Course.sequelize?.query(
      `SELECT
        courses.id,
        courses.name,
        courses.synopsis,
        courses.thumbnail_url as thumbnailUrl,
        COUNT(users.id) AS likes
      FROM courses
        LEFT OUTER JOIN likes
          ON courses.id = likes.course_id
          INNER JOIN users
            ON users.id = likes.user_id
      GROUP BY courses.id
      ORDER BY likes DESC
      LIMIT 10;`
    )

    if (results) {
      const [topTen, metada] = results
      return topTen
    } else {
      return null
    }
  },

// ...

}

• Agora é só adicionar o método no controlador:

// src/controllers/courses-controller.ts

import { Request, Response } from 'express'
import { getPaginationParams } from '../helpers/getPaginationParams'
import { courseService } from '../services/courseService'
import { likeService } from '../services/likeService'

export const coursesController = {

// ...

  // GET /courses/popular
  popular: async (req: Request, res: Response) => {
    try {
      const topTen = await courseService.getTopTenByLikes()
      return res.json(topTen)
    } catch (err) {
      if (err instanceof Error) {
        return res.status(400).json({ message: err.message })
      }
    }
  }
}

• E por fim criar uma nova rota e incluir o método do controlador:

// src/routes.ts

// ...

router.get('/courses/featured', ensureAuth, coursesController.featured)
router.get('/courses/popular', ensureAuth, coursesController.popular)
router.get('/courses/search', ensureAuth, coursesController.search)
router.get('/courses/:id', ensureAuth, coursesController.show)

// ...