topic Re: How to do a query NXQL with propierties in WHERE clause in Nuxeo Forum

How to do a query NXQL with propierties in WHERE clause

Domingo_Gonzále — Wed, 23 Nov 2016 15:32:35 GMT

Hi, I have a custom type of data in nuxeo, this custom type has some fields inside, and I trying to do a query putting this propierties in the WHERE clause.

For example I can do this in a query on I want to SELECT by title: SELECT * FROM Document WHERE dc:title LIKE '%title%' and it works.

I have some simple custom field and I can do NXQL querys with : "SELECT * FROM Document WHERE custom_type:custom_field LIKE '%text%'" and this works too.

But when I trying to do querys with custom type which has properties in, I don't knwo how I can do this. Any help? Thanks

Re: How to do a query NXQL with propierties in WHERE clause

ioihanguren_ — Thu, 24 Nov 2016 17:06:32 GMT

SELECT * FROM CustomType WHERE schema_prefix:custom_field LIKE '%text%' should work, it works with my custom types. But you have to use the prefix of the schema, no the custom_type (dc is the prefix of the schema dublincore).

Re: How to do a query NXQL with propierties in WHERE clause

Bora_ — Wed, 30 Nov 2016 09:42:02 GMT

You can add a custom property in Custom Properties Configuration named query and insert your query. Example:SELECT * FROM doctype WHERE dc:title ILIKE '?%' AND ecm:parentId='#{currentDocument.id}'. You can use any el expression this way.

Re: How to do a query NXQL with propierties in WHERE clause

Amir_Buzo — Wed, 30 Nov 2016 09:54:12 GMT

First of all what do you mean with custom types that has properties in, you mean query complex fields.

https://doc.nuxeo.com/nxdoc/nxql/ This may help you a lot

Complex Properties You can refer to complex properties in NXQL, after the SELECT, in the WHERE clause, and in the ORDER BY clause (cf NXP-4464).

A complex property is a property of a schema containing <xs:simpleType> lists, or <xs:complexType> subelements or sequences of them.

For complex subproperties, like the length field of the content field of the file schema, you can refer to:

content/length for the value of the subproperty. For simple lists, like dc:subjects, you can refer to:

dc:subjects/3 for the 4th element of the list (indexes start at 0), dc:subjects/* for any element of the list, dc:subjects/*1 for any element of the list, correlated with other uses of the same number after *. For complex lists, like the elements of the files schema, you can refer to:

files/3/file/length for the length of the 4th file (again, indexes start at 0), files/*/file/length for any length files/*1/file/length for any length, correlated with other uses of the same number after *. It's important to note that if you use a * then the resulting SQL JOIN generated may return several resulting rows, which means that if you use the CoreSession.queryAndFetch API you may get several results for the same document.

The difference between * and *1 gets important when you refer to the same expression twice, for instance if you want the documents with an optional attached of given characteristics, you must correlate the queries.

This returns the documents with an attached text file of length 0: