sorting with join...

[Ati Rosselet <at...@gmail.com>]

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

Re: sorting with join... probably JCR-3498 :((

[Lukas Kahwe Smith <sm...@pooteeweet.org>]

On Mar 27, 2013, at 20:27 , rosselet <at...@gmail.com> wrote:

> Hi.. thanks for the feedback.  I thought that SQL1 does not support joins.. I
> guess I'll have another look.  Or are you suggesting a different approach?

SQL1 does indeed not support joins .. but in the few cases I at first used joins, i later realized i could just as well do without.

regards,
Lukas Kahwe Smith
smith@pooteeweet.org

Re: sorting with join... probably JCR-3498 :((

[rosselet <at...@gmail.com>]

Hi.. thanks for the feedback.  I thought that SQL1 does not support joins.. I
guess I'll have another look.  Or are you suggesting a different approach?



--
View this message in context: http://jackrabbit.510166.n4.nabble.com/sorting-with-join-tp4658051p4658081.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.

Re: sorting with join... probably JCR-3498 :((

[Lukas Smith <sm...@pooteeweet.org>]

On Mar 27, 2013, at 9:04 , rosselet <at...@gmail.com> wrote:

> It looks like I'm running into this issue:
> https://issues.apache.org/jira/browse/JCR-3498
> 
> but no sign of it being fixed ...   although it seems to me that this is a
> pretty serious bug..
> Does anyone have more info on it?

in general the JOIN (and SQL2) support in Jackrabbit in 2.x is sort of meh .. it mostly works but edge cases are buggy and performance is not very good. so in general i recommend to use SQL1 as much as possible. That being said Alex did have a go at several issues we pointed out.

regards,
Lukas

Re: sorting with join... probably JCR-3498 :((

[rosselet <at...@gmail.com>]

It looks like I'm running into this issue:
https://issues.apache.org/jira/browse/JCR-3498

but no sign of it being fixed ...   although it seems to me that this is a
pretty serious bug..
Does anyone have more info on it?



--
View this message in context: http://jackrabbit.510166.n4.nabble.com/sorting-with-join-tp4658051p4658064.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.