When filtering based on embedded relation, only the embedded child that meets the criteria appears in the parent result

[squaick]

Hi, first of all, thank you for this fantastic package.

I have this problem where I am using paginate;

paginate(query, this.jobRepository, {
  relations: [< Reducted > 'tags', 'top3tag'],
  sortableColumns: [
    < Reducted >
    'top3tag.name',
    'tags.name',
  ],
  searchableColumns: [
    < Reducted >
    'tags.id',
    'top3tag.id',
  ],
  defaultSortBy: [
    ['id', 'ASC'],
  ],
  filterableColumns: {
    < Reducted >
    'tags.id': [FilterOperator.EQ],
    'top3tag.id': [FilterOperator.EQ, FilterOperator.IN],
  },
});

Job is the parent, and job has multiple tags.

When I filter using this query; &filter[top3tag.id]=34241

The result is something like this;

{
	"job_name": "bla bla bla",
	"some_other_fields": "bla bla bla",
	"top3tag": [
            {
		"id": 34241,
		"name": "test"
	     },
    ]
}

But normally job has 3 tags like this:

{
	"job_name": "bla bla bla",
	"some_other_fields": "bla bla bla",
	"top3tag": [
      {
		"id": 34241,
		"name": "test"
	  },{
		"id": 34241,
		"name": "test"
	  },{
		"id": 34241,
		"name": "test"
	  },
    ]
}

I want to filter jobs who has the job.top3tag.id = x, not the child top3tag field itself. Can I achieve this in some way?

Also when I updated nestjs-paginate to the latest version I get this error; Module '"nestjs-paginate"' has no exported member 'FilterOperator'.

Many thanks

[phDooY]

For some reason, FilterOperator can now be exported from "nestjs-paginate/lib/operator"

import { FilterOperator } from 'nestjs-paginate/lib/operator';

[ppetzold]

For some reason, FilterOperator can now be exported from "nestjs-paginate/lib/operator"

import { FilterOperator } from 'nestjs-paginate/lib/operator';

oh, you are right. will fix

@suleymankalkan what query does it currently generate, and what query are you looking for?

[squaick]

@ppetzold the first json example is what I get, the second is what I want. Basicly I want to filter the parent who has one of the top3tags, but I want it to list all top3tags that it has

[fmedeiros95]

I have the same problem... This is a problem that comes from TypeORM itself, just see the queries it generates to perform a filter...

select
	"__root"."id" as "__root_id",
	"__root"."criado_em" as "__root_criado_em",
	"__root"."atualizado_em" as "__root_atualizado_em",
	"__root"."hash_fornecedor" as "__root_hash_fornecedor",
	"__root"."matricula" as "__root_matricula",
	"__root"."publica" as "__root_publica",
	"__root"."eletronico" as "__root_eletronico",
	"__root"."tipo_registro" as "__root_tipo_registro",
	"__root"."livro" as "__root_livro",
	"__root"."folha" as "__root_folha",
	"__root"."termo" as "__root_termo",
	"__root"."data_proclamas" as "__root_data_proclamas",
	"__root"."pagina" as "__root_pagina",
	"__root"."status" as "__root_status",
	"__root"."data_publicacao" as "__root_data_publicacao",
	"__root"."protocolo" as "__root_protocolo",
	"__root"."assinatura" as "__root_assinatura",
	"__root"."serventia_id" as "__root_serventia_id",
	"__root"."edital_id" as "__root_edital_id",
	"__root_edital"."id" as "__root_edital_id",
	"__root_edital"."criado_em" as "__root_edital_criado_em",
	"__root_edital"."atualizado_em" as "__root_edital_atualizado_em",
	"__root_edital"."ano" as "__root_edital_ano",
	"__root_edital"."numero" as "__root_edital_numero",
	"__root_edital"."paginas" as "__root_edital_paginas",
	"__root_edital"."publicado" as "__root_edital_publicado",
	"__root_serventia"."id" as "__root_serventia_id",
	"__root_serventia"."criado_em" as "__root_serventia_criado_em",
	"__root_serventia"."atualizado_em" as "__root_serventia_atualizado_em",
	"__root_serventia"."cns" as "__root_serventia_cns",
	"__root_serventia"."nome" as "__root_serventia_nome",
	"__root_serventia"."nome_reduzido" as "__root_serventia_nome_reduzido",
	"__root_serventia"."status" as "__root_serventia_status",
	"__root_serventia"."telefone" as "__root_serventia_telefone",
	"__root_serventia"."email" as "__root_serventia_email",
	"__root_serventia"."estado" as "__root_serventia_estado",
	"__root_serventia"."estadoSigla" as "__root_serventia_estadoSigla",
	"__root_serventia"."municipio" as "__root_serventia_municipio",
	"__root_serventia"."municipioNome" as "__root_serventia_municipioNome",
	"__root_serventia"."token" as "__root_serventia_token",
	"__root_serventia"."token_ativo" as "__root_serventia_token_ativo",
	"__root_serventia"."responsavel_id" as "__root_serventia_responsavel_id",
	"__root_conjuges"."id" as "__root_conjuges_id",
	"__root_conjuges"."criado_em" as "__root_conjuges_criado_em",
	"__root_conjuges"."atualizado_em" as "__root_conjuges_atualizado_em",
	"__root_conjuges"."cpf" as "__root_conjuges_cpf",
	"__root_conjuges"."nome" as "__root_conjuges_nome",
	"__root_conjuges"."novo_nome" as "__root_conjuges_novo_nome",
	"__root_conjuges"."sexo" as "__root_conjuges_sexo",
	"__root_conjuges"."data_nascimento" as "__root_conjuges_data_nascimento",
	"__root_conjuges"."nacionalidade" as "__root_conjuges_nacionalidade",
	"__root_conjuges"."pais_nascimento" as "__root_conjuges_pais_nascimento",
	"__root_conjuges"."texto_livre_ocupacao" as "__root_conjuges_texto_livre_ocupacao",
	"__root_conjuges"."estado_civil" as "__root_conjuges_estado_civil",
	"__root_conjuges"."proclama_id" as "__root_conjuges_proclama_id"
from
	"public"."proclama" "__root"
left join "public"."edital" "__root_edital" on
	"__root_edital"."id" = "__root"."edital_id"
left join "public"."serventia" "__root_serventia" on
	"__root_serventia"."id" = "__root"."serventia_id"
left join "public"."conjuge" "__root_conjuges" on
	"__root_conjuges"."proclama_id" = "__root"."id"
where
	( ("__root"."data_publicacao" between '2023-03-01' and '2023-03-07'
		and "__root_conjuges"."nome" ilike '%Fulano 1%') )
	and ( "__root"."id" in (1) )
order by
	"__root"."data_proclamas" desc nulls last

the problem is and "__root_conjuges"."nome" ilike '%Fulano 1%'

Sorry for my bad English.

[squaick]

@muxtardo as a work around you can leftjoin it twice with a different alias and filter on the different alias but show the original one. This way you can achieve the result that you want

[fmedeiros95]

@muxtardo as a work around you can leftjoin it twice with a different alias and filter on the different alias but show the original one. This way you can achieve the result that you want

@suleymankalkan how i can do this with this library (nestjs-paginate)?

[podplatnikm]

Bump, any updates?

[ppetzold]

I think it's a normal SQL / typeorm behavior. def needs a custom query to be achieved. something like the below:

@muxtardo as a work around you can leftjoin it twice with a different alias and filter on the different alias but show the original one. This way you can achieve the result that you want