SELECT clause in NXQL queries - part 2
Hi everyone, here’s a follow up on the last Q&A post about select clause in NXQL. I’m going to implement an operation that uses CoreSession.queryAndFetch and show you how to manipulate the result in Studio using MVEL scripting.
First, create an operation. This is quite simple using the operation wizard with Nuxeo IDE. I’ve chosen no input and output. Yes, the operation won’t return anything, instead it will assign the search result to a context variable. Take a look at the code:
package org.nuxeo.sample;
import java.io.Serializable; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map;
import org.nuxeo.ecm.automation.OperationContext; import org.nuxeo.ecm.automation.core.Constants; import org.nuxeo.ecm.automation.core.annotations.Context; import org.nuxeo.ecm.automation.core.annotations.Operation; import org.nuxeo.ecm.automation.core.annotations.OperationMethod; import org.nuxeo.ecm.automation.core.annotations.Param; import org.nuxeo.ecm.core.api.ClientException; import org.nuxeo.ecm.core.api.CoreSession; import org.nuxeo.ecm.core.api.IterableQueryResult; import org.nuxeo.ecm.core.query.sql.NXQL;
/** * @author ldoguin */ @Operation(id=QueryAndFetch.ID, category=Constants.CAT_DOCUMENT, label="QueryAndFetch", description="Query example using queryAndFetch to have specific select clause.") public class QueryAndFetch {
public static final String ID = "QueryAndFetch";
@Context CoreSession coreSession;
@Context OperationContext operationContext;
@Param(name = "query", required = true) String query;
/* * OperationContext variable name. */ @Param(name = "name", required = true) String name;
@Param(name = "language", required = false, widget = Constants.W_OPTION, values = { NXQL.NXQL, "CMISQL" }) protected String lang = NXQL.NXQL;
@OperationMethod public void run() throws ClientException { // Simply execute the query IterableQueryResult itqr = coreSession.queryAndFetch(query, lang, null); List<Map<String, Serializable>> results = new ArrayList<Map<String, Serializable>>(); Iterator<Map<String, Serializable>> it = itqr.iterator(); // put the result in a simpler object like a list while (it.hasNext()) { results.add(it.next()); } // put the result in the operation context operationContext.put(name, results); // always close the iterableQueryResult itqr.close(); }
}
This is a rough implementation. You might want to add pagination or at least a limit for the query result. Now, I’m going to write a simple chain that will display the query result as an information message on the web app. I know it’s not particularly useful, but it makes a good example :)
<extension target="org.nuxeo.ecm.core.operation.OperationServiceComponent" point="chains"> <chain id="testQueryAndFetch"> <operation id="QueryAndFetch"> <param type="string" name="query">select dc:title From Document</param> <param type="string" name="name">queryResult</param> <param type="string" name="language">NXQL</param> </operation> <operation id="Context.RunScript"> <param type="string" name="script"> String titles = 'titles: '; foreach (res : Context['queryResult']) { titles = titles + res.get('dc:title') } Context['titles'] = titles; </param> </operation> <operation id="Seam.AddInfoMessage"> <param type="string" name="message">expr:Context["titles"]</param> </operation> </chain> </extension>
The most important operation here is Context.RunScript. It first instantiates a titles String, then loop into our queryResult variable using a forech. In this loop, we simply concatenate the document titles with the titles object. Then, once it’s done, we assign it to a new variable using Context[‘titles’] = titles;. It’s then easy to retrieve in the following operations. As you can see MVEL scripting can be quite powerful :)