Utilizando o método getIQueryFilters com filtros de tabelas de JOIN's

Data de postagem: May 18, 2010 9:39:15 PM

O IQuery possui a funcionalidade de gerar Join de forma implícita através do recurso do dimensionamento. É possível também criar Joins explícitos através dos métodos innerJoin e outterJoin. Vide Como fazer joins?.

O método getIQueryFilters do DataSource possui a possibilidade de gerar filtros do iQuery sobre as tabelas derivadas de JOINs. Segue, abaixo, um exemplo de uso.

Vamos usar a seguinte definição para o DataSource:

{ includes: [ /* /products/INTEQerp infrastructure/library/iQuery/IQuery.ijs */ -1897036629, /* /products/INTEQorder/dataSources/FiltroCalculadoDePedido.ijs */ -1894442718 ], availableFilters: [ {classKey: -2008879000 /* Pedidos ou Provisões */, prefix: "PED"}, {classKey: -2008889000 /* Títulos*/, prefix: "TIT"} ], availableColumns: [ {classKey: -2008879000 /* Pedidos ou Provisões */, prefix: "PED"}, {classKey: -2008889000 /* Títulos*/, prefix: "TIT" } ], onGetQuery: function DataSource_onGetQuery(dataSource, filters, columns) { var titulos = IQuery.from( -2008889000 /* Titulos */) var iquery = IQuery.from( -2008879000 /* Pedidos ou Provisões */ ) .leftOuterJoin(titulos).on({field: "CHCRIACAO", operator: "=", value: titulos.column("CHCRIACAO") }) .where([filters.getIQueryFilters("TIT_*", "and", titulos), filters.getIQueryFilters("PED_*", "and", pedidos)])

.column(columns.getIQueryColumns("*")); return iquery.toSql(); } }

Executamos o DataSource da seguinte forma:

/* /products/custom/Testes/paulorogerio/datasource/Teste.ids */ var dts = new DataSource(40108823); var filters = dts.createFilters([ {name:"TIT_EMISSAO_START", operator: ">=", value: new Date()}, {name:"TIT_EMISSAO_END", operator: "<=", value: new Date()} ]); var columns = dts.createColumns([{name: "PED_CHAVE"}]); dts.getQuery(filters, columns);

Esta é a consulta SQL gerada pelo exemplo acima:

select PEDIDO2.CHAVE as PED_CHAVE from PEDIDO PEDIDO2 left outer join TITULO TITULO1 on ( PEDIDO2.CHCRIACAO = TITULO1.CHCRIACAO) where ( TITULO1.EMISSAO >= '02/02/2010' and TITULO1.EMISSAO <= '02/02/2010') and (( TITULO1.CLASSE is null or TITULO1.CLASSE in (-2008889000, -2008888890,-2008888790,-1899998525,-1899998524,-1899998370, -1899998369,-1899998367, -1899933806,51518,51519,35337524, 35337539,35337623,35337638))) and ( PEDIDO2.CLASSE in (-2008879000,-1899999820,-1899999819,-1899999818, -1899999817,-1899999814,-1899999812,-1899999754,-1899999752, -1899999480,-1899999479,-1899999478,-1899999477,-1899999476, -1899999475,-1899999474,-1899999473,-1899999472,-1899999471, -1899999470,-1899999469,-1899999468,-1899999467,-1899999466, -1899999465,-1899999383,-1899999300,-1899999299,-1899999295, -1899999289,-1899999288,-1899999287,-1899999286,-1899999285, -1899999278,-1899999274,-1899999273,-1899999272,-1899999271, -1899999269,-1899999104,-1899999103,-1899998951,-1899998950, -1899998949,-1899998948,-1899998947,-1899998946,-1899998945, -1899998944,-1899998943,-1899998942,-1899998941,-1899998940, -1899998939,-1899998925,-1899998923,-1899998922,-1899998921, -1899998920,-1899998919,-1899998918,-1899998917,-1899998916, -1899998915,-1899998914,-1899998913,-1899998912,-1899998901, -1899998382,-1898190299,-1898190298,-1898190297,-1898190136, -1898187711,-1898187709,-1898187600,-1898187552,-1898187551, -1897054192,-1897054190,-1897054189,-1897054188,-1897054187, -1897054186,-1897054185,-1897054184,-1897054183,-1897054182, -1897054181,-1897054180,-1897054179,-1897054178,-1897053396, -1897053239,-1897053225,-1897053120,-1897053119,-1897053118, -1897053117,-1897052491,-1897052280,-1897052279,-1897052278, -1897052277,-1897052276,-1897052275,-1897052154,-1897050841, -1897048773,-1897048772,-1897048771,-1897048743,-1897048742, -1897048700,-1897048154,-1897048153,-1897036337,-1896648405, -1896648404,-1896648282,-1896648274,-1896648273,-1896648229, -1896648217,-1896648208,-1896648207,-1896648206,-1896648197, -1896648195,-1896648194,-1896648193,-1896648190,-1896648103, -1896647767,-1896646976,-1896646955,-1896646934,-1896646914, -1895947335,-1895947334,-1895947333,-1895946241,-1895836214, -1894743941,-1894543798,-1894543796,-1894543717,-1894443036, -1894442985,1990,12682,34011,34015,34017,34018,34131,34440,38715, 38754,42351,65941,79382,162703,177781,183608,183609,186447,188473, 2025108,5320945,6024675,12060574,35337554,35337574,35915925, 35915943,35915961,35915979,35915997,36762192,36762211,37428549, 37859429,38292040,38530826,40035178,40035242))