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.

[afaust]

It would have been great to lead with this example so your use case becomes clear. You don't want to join two types, but you want to query for an aggregate set of metadata from two distinct documents that share some ID as foreign/primary key.

That is not what either CMIS or FTS queries were ever intended for.

Normally in such a case, it would be appropriate to model the metadata of the offer/order via aspects and apply a custom metadata inheritance logic when you link an offer and order together. In a past project I have also developed such an inheritance feature that avoids duplicating the data on the database but transparently provides it for indexing in SOLR, so you could query an offer based on the metadata set on the order (and potentially vice-versa). That module is commercial though and I no longer work for the company where I developed that.

[s_palyukh]

Yes, I understand, but it is a business requirement that has to be done, so we must find a way.

Currently, we see 2 ways:

1) Direct sql query
2) The more interesting way is to override the webscript "webscript.org.alfresco.repository.solr.nodesMetaData.post" where we will add some logic for solr to index properties from deal type as part of offer type. Solr calls this webscript to get metadata that should be indexed, so if we add additional fields to the response then they will be indexed and we can search for them.

Simple test displays that it is possible. Do you have any knowledge about solr? Maybe you know some issues that we may face?