How to join properties from 2 types? Any ideas?

[s_palyukh]

Hi to everyone.

We got the requirement to join properties from 2 different types, we tried to use CMIS, but it supports JOIN only for aspect and can't be used for 2 types. Maybe is there some ability to use SOLR for that needs, something like indexing 2 alfresco nodes as 1 Solr document? 

Maybe, you can offer another way?

[afaust]

If those types are 100% orthogonal then there is no way to join them. But you are wrong - CMIS queries actually supports JOIN on types (at least I recently verified this for a query merging algorithm I wrote using cmis-alfresco query language). But the JOIN will not yield any results if a document does not have both types (one being a base / parent type of the other).

[s_palyukh]

@Axel thank you for feedback.

Seems, we will need to write sql query to do what we need

[afaust]

Just use FTS query instead with an OR on the TYPE condition. No need to write SQL for this.

[s_palyukh]

I don't think that FTS can help...

Let me explain more detailed, we have 2 types: offer and deal.

Properties of the offer:

• id
• price
• dealId

Properties of deal

• id
• companyName
• companyType

And we need to find something like:

!select * from offer as o join deal as d on o.dealId=deal.id where d.companyType="Bank"

It is a simple case above. We have much more fields and about 100000 deals, so speed is the key.