Request for distinctOn Method

[zjr]

Environment

Knex version: 0.19.5
Database + version: PostgreSQL v11.5
OS: MacOS

Feature discussion / request

Background

This is about adding a distinctOn method to Knex, in addition to the existing distinct. This has been previously discussed in these issues: #51, #681.

Use Case

As mentioned in the above referenced issues, it's currently possible to use DISTINCT ON through raw, but there are some problems with this.

1. By default, Knex will add a , after the clause which is invalid in PostgreSQL and causes the query to fail. For example it will turn out looking like select distinct on ("column1"), "column2" from "tbl_name".
2. If one were to write the entire select statement with raw, the query would no longer be reusable if additional selects needed to be added because Knex will append the select with a comma.
3. The best way I've come to to work around this is to do something like knex('table').select(knex.raw('distinct on (??) ??', ['id', 'id'])). The problem with this is the possibility for duplicate columns in select.

What would support this?

A distinctOn function that would keep the distinct on portion separate from other select's.

API Proposal

knex('users').distinctOn('id').select('name');

Resulting in

select distinct on ("id") "name" from "users";

[elhigu]

Sounds fine to me... If the method is called with any other DB (not supporting it) error should be thrown, Anyone... feel free to implement and send PR for this 👍

[kibertoad]

Released in 0.20.2

[jsilveira2]

Hi @kibertoad , i updated my knex version to 0.20.2, but i still cant use distinctOn.
Knex return: knex(...).distinctOn is not a function;

Do i have to call the method like this? knex('table1').distinctOn('columnA').

[lorefnon]

@jsilveira2 Can you please provide an example of how you are using it ?

[jsilveira2]

@lorefnon Yes of course, thanks for answer.

My complete query:

        const queryWhen = knex.raw(`
		        pe.id,
		        pp.fl_permitir,
		        rec.ds as "recurso",
		        op.ds as "operacao",
		        ROW_NUMBER() OVER(PARTITION BY pp.id_permissao ORDER BY CASE pp.id_perfil
		    WHEN '2873d916-ebda-4971-9e4b-77a73719c6f9' THEN 0 END) AS rk
        `);

        const query = knex.with('permissoes', qb => {
            qb.select(queryWhen).from('apl_permissao as pe').leftJoin('adm_perfil_permissao as pp', function() {
                this.on('pe.id', '=', 'pp.id_permissao').andOn('cd_conta', '=', 1).onIn('id_perfil', [ '2873d916-ebda-4971-9e4b-77a73719c6f9' ]);
            }).leftJoin(
                'apl_recurso as rec', 'rec.id', 'pe.id_recurso'
            ).leftJoin(
                'apl_operacao as op', 'op.id', 'pe.id_operacao'
            );
        }).select(
            'im.id',
            'im.ds',
            'im.ds_route',
            'im.ds_classe_icone',
            'im.id_tela',
            'im.id_item_menu_pai',
            'p.id',
            'p.fl_permitir',
            'tp.id_permissao'
        ).distinctOn('im.id').from('permissoes as p').join(
            'apl_tela_permissao as tp', 'p.id', 'tp.id_permissao'
        ).join(
            'apl_item_menu as im', 'tp.id_tela', 'im.id_tela'
        ).where(
            'p.rk', 1
        ).whereRaw(
            condition
        ).toString();

I using after select, but i try before and the same error is returned.

My final query have to be like this:

WITH permissoes AS (
    SELECT
             pe.id,
             pp.fl_permitir,
             rec.ds as "recurso",
             op.ds as "operacao",
             ROW_NUMBER() OVER(PARTITION BY pp.id_permissao ORDER BY CASE pp.id_perfil
          WHEN '2873d916-ebda-4971-9e4b-77a73719c6f9' THEN 0 END) AS rk
     FROM
             public.apl_permissao pe
     LEFT JOIN public.adm_perfil_permissao pp ON (pe.id = pp.id_permissao and cd_conta = 1 and id_perfil in ('2873d916-ebda-4971-9e4b-77a73719c6f9'))
     LEFT JOIN public.apl_recurso rec ON (rec.id = pe.id_recurso)
     LEFT JOIN public.apl_operacao op ON (op.id = pe.id_operacao)
)
SELECT DISTINCT ON (im.id)
    im.id,
     im.ds,
     im.ds_route,
     im.ds_classe_icone,
     im.id_tela,
     im.id_item_menu_pai,
     p.id,
     p.fl_permitir,
    tp.id_permissao
FROM
    permissoes p
     JOIN apl_tela_permissao tp ON (p.id = tp.id_permissao)
     JOIN apl_item_menu im ON (tp.id_tela = im.id_tela)
WHERE
    p.rk = 1
    and p.fl_permitir = true

Sorry for my bad english.

[dakkafex]

How does this function work, i cant find distinctOn in the documentation