This problem exists in Hibernate itself as well and, contrary to some comments I've seen in the bug tracker, I believe it is a bug in the Criteria API and not in HQL.
A trivial example
I have a Store type representing a shop which has a collection, Products, containing Product types the shop stocks e.g. "Golf Balls", "Bananas", "Hats" etc. I want to get all the stores who stock both Golf Balls and Hats.
In HQL this would be :
SELECT s
FROM Store AS s
INNER JOIN s.Products AS prod1
INNER JOIN s.Products AS prod2
WHERE prod1.Type = 'Golf Balls'
AND prod2.Type = 'Hats'...pretty straight forward and works fine.
In Criteria API this would be:
IList stores = sess.CreateCriteria(typeof(Store))
.CreateAlias("Products", "prod1")
.CreateAlias("Products", "prod2")
.Add( Expression.EqProperty("prod1.Type", "Golf Balls") )
.Add( Expression.EqProperty("prod2.Type", "Hats") )
.List();...again straight forward and seems logical but this produces an error...
NHibernate.QueryException: duplicate association path Products
As I said, it seems like a pretty solid candidate for a bug and it's odd considering surely the meaning of CreateAlias is that I want to use the same association more than once so need to alias it to different labels.
Unfortunately there's no way to get around this issue if you need distinct association joins like the above example and looking at the NHibernate code it doesn't seem like an easy fix. If, however, you can apply your criterion or sorts to the same alias then there is a workaround.
Workaround
If we take a look at this handy NHibernate API reference we see that the two implementing classes for ICriteria are NHibernate.Impl.CriteriaImpl and NHibernate.Impl.CriteriaImpl.Subcriteria.
CriteriaImpl is the root criteria you get calling CreateCriteria on ISession and Subcriteria you get with every call to CreateCrteria or CreateAlias on ICriteria.
First you need to retrieve the root CriteriaImpl for your working ICriteria. Your working ICriteria may be the root but if it isn't you need to recurse up through the Parent property until you reach the CriteriaImpl object.
CriteriaImpl has an IterateSubcriteria method which returns an IList of all its Subcriteria descendants. You can loop through this list checking the Parent and Path properties of each item. The Parent because the value of Path is relative and you're only interested in what will be sibling Subcriteria to the one you're about to add.
If you find a match you can retrieve its alias from the Alias property, otherwise you can add a new alias to your working ICriteria.
Update - Jan 2014
It seems this bug is still not fixed in NHibernate (or Hibernate for that matter) and that it may also affect the LINQ provider. The relevant issues links are:
- HHH-879: Enable joining the same association twice with Criteria
This is the original Hibernate bug which was ported to the .NET version. - NH-2016: Duplicate Association Path when creating multiple aliases
This is the same issue against NHibernate. - NHLQ-43: Duplicate alias QueryException thrown when executing a modified linq query
Although this is a issue reported against the now deplrecated NHLQ project it's unclear whether the issue is fixed. - NH-3472: Joining to association twice using criteria causes: NHibernate.QueryException: duplicate association path
This is a duplicate of the NHibernate bug
I'm very tempted to have a go at fixing this myself given there still seem to be a few people struggling with it. Will post another update if I get anywhere.
10 comments:
I cant understand your second solution. In my aplication I needs to use the criteria API (more dinamic). Could you show me a example to use?
My problem is something like that:
I need to filter two values of adress
like this:
client.address.street = 'Jordan Street'
client.adress.number = '123'
but when I try to filter using criteria and alias. I got the error: duplicate path.
could you help me?
sorry my bad english
If you know what your alias for address is then all you need to do is ensure you just add the one alias and add both expressions using the same alias e.g.
criteria
.CreateAlias("address", "addr")
.Add( Expression.EqProperty("addr.street", "Jordan Street") )
.Add( Expression.EqProperty("addr.number", "123")
If the expressions are added at different places in your code and you're dynamically generating the alias value then you need to programmatically find the first ICriteria that aliases "address". So, assuming you created your root criteria on client i.e.
ICriteria clientCriteria = sess.CreateCriteria(typeof(client))
you call the IterateSubcriteria on this which returns an IEnumerable you loop over until you find one with a Path equal to "address". You then retrieve the value of Alias for this Subcriteria to use in your expression.
*you call the IterateSubcriteria on this which returns an IEnumerable you loop over until you find one with a Path equal to "address". You then retrieve the value of Alias for this Subcriteria to use in your expression.
I cant understand this part. Im using hibernate3... and in my api not exist ICriteria. I Iterate using iterateSubcriteria:
CriteriaImpl criteriaImpl = (CriteriaImpl)criteria;
Iterator it = criteriaImpl.iterateSubcriteria();
while(it.hasNext()){
Subcriteria subCriteria = (Subcriteria) it.next();
System.out.println(subCriteria.getPath());
System.out.println(subCriteria.getParent().getAlias());
}
and I saw where its use the same path, but the alias property cant solve this problem. Im using dinamically alias diferents using a sequence number at the end of alias string.
Im using DetachedCriteria to create the criteria
DetachedCriteria dc = DetachedCriteria.forClass(type,"aux");
dc.getExecutableCriteria(session);
I read this issue HHH-879 at Jira. Nobody founds a good solution for this problem...
Sorry, but i couldnt understood your last paragraph...
and thanks once
I'm using NHibernate, the .NET version of Hibernate. This solution wasn't related to DetachedCriteria so you may not be able to use it in the same way. If it is your code generating the aliases then at the point where the alias is about to be created you should check the Criteria tree first to see if an alias of that particular path already exists and if it does you use that instead of generating a new one.
Hello,
I ran across a similar situation and was wondering if you were able to find a solution for this issue.
Any advice would be appreciated!
A "distinct association join" as described is exactly what I need. Was there ever any movement on this issue from 2008 to 2014 (...today)? I've been searching but I haven't seen any clear answers yet. Thanks.
It doesn't seem so, no, I've updated the post with some details. As I said in the update, I'm going to have a look at this and see if I can fix it myself.
Thanks for the response, and the update.
As a workaround "after the fact", I'm currently using a QueryOver that joins with an alias just *once* and then includes an OR disjunction of the multiple properties I'm seeking from the join (...even though I really want an AND of the properties).
Then I use LINQ on the non-distinct result list to figure out which objects satisfy *all* the property criteria by seeing which ones were returned multiple times (...equal to the number of properties I'm seeking from the join).
It's ugly and a waste of querying bandwidth and it wouldn't scale. But it gets the right results for now.
And all for a query, as you point out, that is well understood from a database perspective and not unusual in any way.
Another workaround is to just create multiple mappings for the same entity and join off of those. It's hacky but NH no longer chokes.
Thank you for this post and the link to all the tickets still open. Now I at least know that what I try to do is actually not possible.
Background: I recently started working on a project that uses NHibernate and I'd like to join the same table three times, with different join-conditions. Adding these conditions to the where-clause will unfortunately not work.
Although I am not happy about the end result, thanks for the confirmation!
Roelof
Post a Comment