jackrabbit-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ati Rosselet <ati.rosse...@gmail.com>
Subject sorting with join...
Date Tue, 26 Mar 2013 22:25:28 GMT
Hi,
I'm having a bit of trouble setting up an SQL2 query for sorting that
uses a join.
Basically I have 2 types of Objects, Company and Contact that are
joined with Company.manager = Contact.[jcr:uuid].  The type of the
node is specified by the crm_type attribute.
My Current query looks like this:

SELECT crm.* FROM [nt:unstructured] AS crm  LEFT OUTER JOIN
[nt:unstructured] AS Contact ON crm.[manager] = Contact.[jcr:uuid]
WHERE ISDESCENDANTNODE(crm,'/1/') AND  crm.isActive = true AND
crm.crm_type = 'Organization'  AND (ISDESCENDANTNODE(Contact,'/1/')
AND Contact.isActive = true AND Contact.crm_type = 'Contact' )  ORDER
BY Contact.name ASC

where the restriction of ISDESCENDENTNODE limits to searching within
user#1's path.
Now this works.. but I only get rows where I have both objects, where
I ideally want to get all Organizations and only order by the manager
contact's name if is is defined, otherwise have that as null.
What is wrong with my query? or am I doing something fundamentally
wrong? The results I am getting seem to be what I would expect from an
INNER JOIN...

I am using jackrabbit 2.4.1.   If this is a known bug or something, I
can upgrade easily to 2.4.3 or 2.6...

Thanks
Ati

Mime
View raw message