ContentInstanceDBQuery

Alison_Medeiros_De_Souza

Is possible create a sub query using the ContentInstanceDBQuery? How?

public List getComunicados(String tipo, String idioma, String ano, int qtdePagina, int numPagina) throws ApplicationException, ValidationException

{

int startIndex = 0;

int endIndex = 0;

 

ArrayList retorno = new ArrayList();

ContentInstanceWhereClause mainClause = new ContentInstanceWhereClause();

mainClause.setMatchAny(false);

 

ContentInstanceWhereClause ctClause = new ContentInstanceWhereClause();

ContentType ctd = (ContentType)ContentType.findByName("RI_COMUNICADO");

ContentInstanceDBQuery queryB = new ContentInstanceDBQuery(new ContentTypeRef(ctd));

mainClause.addWhereClause(ctClause);

 

if(tipo != null && tipo != "") {

ContentInstanceWhereClause tipoClause = new ContentInstanceWhereClause();

tipoClause.checkAttribute("ARTIGO-DES-PAIS-ATR", StringQueryOp.EQUAL, tipo, false);

mainClause.addWhereClause(tipoClause);

}

 

if(ano != null && ano != "") {

Date dtInicial = new Date((Integer.parseInt(ano) - 1900),0,1,0,1,1);

Date fim = new Date((Integer.parseInt(ano) - 1900),11,31,23,59,59); 

 

ContentInstanceWhereClause dateClause = new ContentInstanceWhereClause();

dateClause.checkAttribute("ARTIGO-DAT-ARTG", DateQueryOp.AFTER, dtInicial);

dateClause.checkAttribute("ARTIGO-DAT-ARTG", DateQueryOp.BEFORE, fim);

 

dateClause.setMatchAny(false);

mainClause.addWhereClause(dateClause);

}

 

if(idioma != null && idioma != "") {

ManagedObjectWhereClause ctChannels = new ContentInstanceWhereClause();

 

if(idioma.equals("pt")) {

ChannelRef channelPT = new ChannelRef("701377348248d310VgnVCM100000d8193a0aRCRD");

ctChannels.checkChannel(ObjectQueryOp.EQUAL, channelPT);

}

else if(idioma.equals("en")) {

ChannelRef channelEN = new ChannelRef("477f2c250839d310VgnVCM100000d8193a0aRCRD");

ctChannels.checkChannel(ObjectQueryOp.EQUAL, channelEN);

}

else if(idioma.equals("es")) {

ChannelRef channelES = new ChannelRef("4b3986dbcf5dd310VgnVCM100000d8193a0aRCRD");

ctChannels.checkChannel(ObjectQueryOp.EQUAL, channelES);

}

 

mainClause.addWhereClause(ctChannels);

}

 

queryB.setWhereClause(mainClause);

 

RequestParameters requestParameters = new RequestParameters();

requestParameters.setTopRelationOnly(true);

 

IPagingList iPagingList = QueryManager.execute(queryB, requestParameters);

qtdeTotal = iPagingList.size();

 

// paginacao

endIndex = startIndex + qtdePagina;

numPagina = numPagina+1;

 

List list = iPagingList.subList(numPagina, endIndex);

 

return list;

}

David_Stiles

No, it is not possible to specify a nested sub-query in another query using ContentInstanceDBQuery (or any other type of DBQuery). However, you can execute the sub-query as a separate query and then use the results of that query as parameters to a subsequent query.

 

Beyond this, I’m not sure what your actual point/question is in posting this code, but I do see a couple of issues with it that you should be aware of.

 

First, just so you can verify this is what you intended, this is what the query generated by this code will effectively look like:

 

select (contentInstances of type RI_COMUNICADO) where

                ( () and (ARTIGO-DES-PAIS-ATR = ?) and (ARTIGO-DAT-ARTG > ? and ARTIGO-DAT-ARTG < ?) and (channel = ?) )

 

In addition, there are two things I want to point out here.

 

<![if !supportLists]>1)     <![endif]>Note that the first term of the where clause is empty. This is due to your addition of ctClause to mainClause without any conditions being set on ctClause. This is harmless, but there’s no point in nesting an empty ContentInstanceWhereClause here.

<![if !supportLists]>2)     <![endif]>A bigger problem I see though is in your configuration of the ctChannels where clause. The ChannelRef values you are passing into checkChannel() are being constructed with the wrong type of ids (i.e. they have the ‘RCRD’ type code). These ids are the ones you would get from Channel.getContentManagementId(), which would be of type ManagedObjectVCMRef. What you need here are the ids you would get from Channel.getManagedObjectRef(), which are of ManagedObjectRef (specifically ChannelRef in the case of channels). These ids will have a type code of ‘___’.

 

 

 

From: eLink Entry: Discussion Group - Web Experience Management [mailto:v7webcontentmanagement@elinkkc.opentext.com]
Sent: Friday, August 16, 2013 8:54 AM
To: eLink Recipient
Subject: ContentInstanceDBQuery

 

ContentInstanceDBQuery

Posted byalison.souza@itau-unibanco.com.br (De Souza, Alison Medeiros) On 08-16-2013 09:49

 

Is possible create a sub query using the ContentInstanceDBQuery? How? public List getComunicados(String tipo, String idioma, String ano, int qtdePagina, int numPagina) throws ApplicationException, ValidationException { int startIndex = 0; int endIndex = 0;   ArrayList retorno = new ArrayList(); ContentInstanceWhereClause mainClause = new ContentInstanceWhereClause(); mainClause.setMatchAny(false);   ContentInstanceWhereClause ctClause = new ContentInstanceWhereClause(); ContentType ctd = (ContentType)ContentType.findByName("RI_COMUNICADO"); ContentInstanceDBQuery queryB = new ContentInstanceDBQuery(new ContentTypeRef(ctd)); mainClause.addWhereClause(ctClause);   if(tipo != null && tipo != "") { ContentInstanceWhereClause tipoClause = new Cont entInstanceWhereClause(); tipoClause.checkAttribute("ARTIGO-DES-PAIS-ATR", StringQueryOp.EQUAL, tipo, false); mainClause.addWhereClause(tipoClause); }   if(ano != null && ano != "") { Date dtInicial = new Date((Integer.parseInt(ano) - 1900),0,1,0,1,1); Date fim = new Date((Integer.parseInt(ano) - 1900),11,31,23,59,59);    ContentInstanceWhereClause dateClause = new ContentInstanceWhereClause(); dateClause.checkAttribute("ARTIGO-DAT-ARTG", DateQueryOp.AFTER, dtInicial); dateClause.checkAttribute("ARTIGO-DAT-ARTG", DateQueryOp.BEFORE, fim);   dateClause.setMatchAny(false); mainClause.addWhereClause(dateClause); }   if(idioma != null && idioma != "") { ManagedObjectWhereClause ctCha nnels = new ContentInstanceWhereClause();   if(idioma.equals("pt")) { ChannelRef channelPT = new ChannelRef("701377348248d310VgnVCM100000d8193a0aRCRD"); ctChannels.checkChannel(ObjectQueryOp.EQUAL, channelPT); } else if(idioma.equals("en")) { ChannelRef channelEN = new ChannelRef("477f2c250839d310VgnVCM100000d8193a0aRCRD"); ctChannels.checkChannel(ObjectQueryOp.EQUAL, channelEN); } else if(idioma.equals("es")) { ChannelRef channelES = new ChannelRef("4b3986dbcf5dd310VgnVCM100000d8193a0aRCRD"); ctChannels.checkChannel(ObjectQueryOp.EQUAL, channelES);   }       mainClause.addWhereClause(ctChannels);   }       queryB.setWhereClause(mainClause);       RequestParameters requestParameters = new RequestParameters();   requestParameters.setTopRelationOnly(true);       IPagingList iPagingList = QueryManager.execute(queryB, requestParameters);   qtdeTotal = iPagingList.size();       // paginacao   endIndex = startIndex + qtdePagina;   numPagina = numPagina+1;       List list = iPagingList.subList(numPagina, endIndex);       return list;   }

---

[To post a comment, use the normal reply function]
Forum:	Discussion Group - Web Experience Management
Content Server:	Knowledge Center