How to create criteria with many-to-many relation

aruss · **Posted:** Fri Apr 18, 2008 5:24 am

Hi, I try to fetch objects with many-to-many relation, but I don’t really know how to do that
I use nhibernate since view days and try to understand and use it for my current project.

I use NHibernate-2.0.0 with MS-SQL 2005, here are my files

First class, is a main class contains needed data.

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="MediaLibrary.Core.Content, MediaLibrary.Core" table="CONTENTS" lazy="false">
    <id name="Id" column="id" type="int">
      <generator class="native" />
    </id>
    
    <property name="Path" column="path" type="String" length="1024"/>
    <property name="Rating" column="rating" type="int"/>

    <bag name="Keywords" table="CONTENTS_KEYWORDS" cascade="all">
      <key column="contentid"/>
      <many-to-many column="keywordid"
         class="MediaLibrary.Core.Keyword, MediaLibrary.Core"/>
    </bag>
  </class>
</hibernate-mapping>

And here the second contains Keywords (Tags) to find a content class

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="MediaLibrary.Core.Keyword, MediaLibrary.Core" table="KEYWORDS" lazy="false">
    <id name="Id" column="id" type="int">
      <generator class="native" />
    </id>
    
    <property name="Value" column="value" type="String"/>
  </class>
</hibernate-mapping>

Here is my many-to-many relation in sql

I try to execute this query

Code:

SELECT CONTENTS.*
FROM CONTENTS_KEYWORDS INNER JOIN
 KEYWORDS ON CONTENTS_KEYWORDS.keywordid = KEYWORDS.id INNER JOIN
 CONTENTS ON CONTENTS_KEYWORDS.contentid = CONTENTS.id
WHERE   (KEYWORDS.Value LIKE 'foo' OR KEYWORDS.Value LIKE 'bar' OR ... )

Which way should I prefer? Criteria, HQL or SQL?
I tried to create a criteria because I have to build where statement dynamically

Code:

DetachedCriteria query = DetachedCriteria.For(typeof(Keyword));
Disjunction disj = new Disjunction();

foreach (string key in keywords)
    disj.Add(Expression.Like("Value", key));

query.Add(disj);

ICriteria crit = SessionProvider.Default.Session.CreateCriteria(typeof(Content));

// .... ???

I will be happy with any help guys

aruss · **Posted:** Mon Apr 21, 2008 6:26 am

I done it by this way, but It not realy nice

Code:

// HACK: The big one 
Disjunction disj = new Disjunction();

foreach (string key in keywords)
    disj.Add(Expression.Like("Value", "'" + key + "'"));

string whereStatement = disj.ToString(); 
whereStatement = "WHERE " + whereStatement.Replace("Value", "KEYWORDS.Value"); 

string sqlQuery = "SELECT CONTENTS.* " +
              "FROM CONTENTS_KEYWORDS INNER JOIN " +
              "KEYWORDS ON CONTENTS_KEYWORDS.keywordid = KEYWORDS.id INNER JOIN " +
              "CONTENTS ON CONTENTS_KEYWORDS.contentid = CONTENTS.id " + whereStatement +
              " GROUP BY CONTENTS.id, CONTENTS.path, CONTENTS.rating";

ISQLQuery q = SessionProvider.Default.Session.CreateSQLQuery(sqlQuery).AddEntity(typeof(Content));

return (List<Content>)q.List<Content>();

how I can do this by the best way

greets aruss

rolandz · **Posted:** Mon Apr 21, 2008 8:03 am

Try this:

Code:

ICriteria c = session.CreateCriteria(typeof(Content));

c.CreateCriteria(typeof(Keyword))
  .Add(Expression.Like("Value", ...)
.List<Content>();

For me such an approach worked fine but I've had just one expression for where clause for the sub criteria. I think it should work with multiple as well...

In general the native SQL query should be the less wanted, in my opinion. The choice between HQL and criteria queries mostly depends on the scenario. However, my experiens shows that the criteria are more flexible in case when you need to build it in the runtime (whant to add sbth depending on the application state - i.e. order by clause) while the named queries (HQL) are nice when you whant to have them in one dedicated place. I've been using criterias then decided to implement using named queries but now I am again considering criterias due to their flexibility during the runtime... I just really need to control ordering (i.e. when a user clicks on the column in the web page).

Hope this helps.

thinkpad · **Joined:** Fri Apr 11, 2008 1:48 am **Posts:** 36

Can somebody can explain this criteria? I did still not understand this example. I want to create an criteria on many-to-many relations. For example:

Entity T1 with a Relation to T1_T2 (only IDs) Objects
Entity T2 with a Relation to T1_T2 (only IDs) Objects

How can I create a Critera which has restrictions on T1 and restricitions to T2. T2 should be joined first to T1 with the relation in T1_T2.

Anybody got an idea?

vaserqno · **Joined:** Wed Mar 26, 2008 7:29 am **Posts:** 2

Hi! aruss did you solved with Criteria?

I've the same problem. my case is as follows

content
---------
id
language

content-wordList
-------------------
cWordId
aId
aLanguage

wordList
-------------
wordid
wordName
wordLanguage

And I looking for all the contents that have 'red' and 'blue' like a word
But, I can search only one word

Any help or reference is really important
Thanks !!!!

thinkpad · **Joined:** Fri Apr 11, 2008 1:48 am **Posts:** 36

I did solve it:

You have to create a second Criteria Object within the old Criteria. After that you can create an createAlias within that new Criteria and you will get to your values.

For Example:

Code:

Criteria crit = _session.createCriteria(Contact.class);
crit.createCriteria("account").createAlias("addresses","addresses").add(Restrictions.eq("addresses.country", "Germany")); // Option of Account class
crit.createAlias("assigned", "assigned"); // Option of Contact class
crit.add(Restrictions.eq("assigned.lastname", "Theo"));