Re: [cmis] discovering containment relationships in queries

[Florent Guillaume <fg@nuxeo.com>]

My use case is that I'm interested in doing queries about documents,  
but some attributes common to a number of them have been factored out  
to their containing folder.

For instance the folders represent countries, and include other  
metadata like, say, country population, and all the documents created  
in a given country are stored in the folder for that country. As an  
example, I'd like to query for all the documents having some  
properties and created in a given country -- or created in a country  
with population > 20M.

So the folder containment is used as a categorization tool. This is  
pretty common I think.

Doing two queries has obvious performance problems in some situations.

Florent

On 1 Jul 2009, at 19:35, Al Brown wrote:
> There's two ways of doing it:
> 1. leverage the repository specific relationship (or parentId  
> property if single) if available (e.g., rep-specific)
> 2. do two queries:
> SELECT ObjectId FROM folder WHERE important = true
> SELECT * FROM document WHERE IN_FOLDER(id1) OR IN_FOLDER(id2) ...  
> (or bunch of getChildren calls)
>
> What are the use cases where filtering the folders are required and  
> then getting contained documents in 1 operation is necessary?
>
>
> Florent Guillaume wrote:
>> Suppose I want to find all documents contained in folders marked
>> "important". Is there a way to express this in CMIS SQL that I'm
>> missing?
>>
>> At first I thought of doing:
>>   SELECT doc.* FROM Document doc JOIN Folder fold ON doc.ParentId =
>> fold.ObjectId WHERE fold.important = true
>>
>> However documents don't have a ParentId attribute (and could have
>> multiple parents when multi-filing is involved).
>>
>> I feel that this is an important query use case. Unless I'm missing
>> something and people tell me how it can be done, then I'll open a  
>> JIRA
>> issue for that. At the moment I only see one option, which is to  
>> allow
>> a single-valued ParentId on documents so that at least the single-
>> filing case can be dealt-with. I'm not sure it's satisfying though,
>> and I'd love to hear others on this.
>>
>> Florent

-- 
Florent Guillaume, Head of R&D, Nuxeo
Open Source, Java EE based, Enterprise Content Management (ECM)
http://www.nuxeo.com   http://www.nuxeo.org   +33 1 40 33 79 87