feathers-knex-distinct.js

const app = require('@feathersjs/feathers')
const service = require('feathers-knex')
const knex = require('knex')

const db = knex({
  client: 'mysql',
  connection: 'mysql://user:pass@localhost/test',
})

app.use('todos', service({
  Model: db,
  name: 'todos',
  multi: true,
  whitelist: ['$distinct'],
}))

app.service('todos').hooks({
  before: {
    find: [
      context => {
        // If {$distinct: true} is included in the query,
        // add `distinct()` to the knex query.
        if (context.params.query.$distinct) {
          delete context.params.query.$distinct
          const knex = context.service.createQuery(context.params)
          context.params.knex = knex.distinct()
          // workaround to remove id from the query:
          // context.params.knex = knex
          //   .clearSelect()
          //   .select(context.params.query.$select)
          //   .distinct()
          return context
        }
      },
    ],
  },
})

async function main() {
  const todosService = app.service('todos')

  // Insert two todos with the same text.
  await todosService.create({id: 1, text: 'Buy eggs'})
  await todosService.create({id: 2, text: 'Buy eggs'})
  
  // Query the todos with distinct. Should only receive one todo as
  // [{text: 'Buy eggs'}] but instead receive two todos as
  // [{id: 1, text: 'Buy eggs'}, {id: 2, text: 'Buy eggs'}] 
  const todos = await app.service('todos').find({
    query: {
      $distinct: true,
      $select: ['text']
    },
  })
}