Many-To-Many: What criteria?

rolandz · **Posted:** Mon May 30, 2005 5:43 am

Hi,

I have two classes:

class Item
{
// All members...
public ISet Categories;
}

and

class Category
{
// All members...
}

These are mapped to three tables: ITEM, CATEGORY and ITEM_CATEGORY, making many-to-many relationship. This is clear so far.

However what criteria shall I build to fetch all items having a given list of categories? If there is no way to use a criteria the please give me a hint for query...

TIA

--
Roland

devonl · **Posted:** Mon May 30, 2005 10:43 am

depending on your mapping files:

Code:

public IList GetItemsByCategory (Category category) {
  return session.CreateCriteria(typeof(ItemCategory))
    .Add(Expression.Eq("Category", category))
    .AddOrder(Order.Asc(ItemName))
    .List();
}

or, for more than one category:

Code:

public IList GetItemsByCategories (ArrayList categories) {
  ICriteria crit = session.CreateCriteria(typeof(ItemCategory));

  foreach (Category cat in categories) {
    crit.Add(Expression.Eq("Category", cat));
  }

  crit.AddOrder(Order.Asc(ItemName))

  return crit.List();
}

these are quick examples. playing with the CreateCriteria interface based on your own mappings will produce better results. Also, this assumes you have an Association Class of type ItemCategory.

-devon

subdigital · **Posted:** Tue May 31, 2005 3:16 pm

I am having a similar problem. I have these 2 classes: User, Role

User contains an IList of Roles

They relate to these 3 tables: Users, Roles, Users_Roles_Join

I take it that I need a third class (an association class)?

Basically what I want to do is this (but in HQL not in SQL)

Code:

SELECT u.*
FROM users u
   INNER JOIN users_roles_join j
      ON u.id = j.user_id
WHERE j.role_id=@ROLEID

how would I accomplish this in HQL?[/code]

devonl · **Posted:** Tue May 31, 2005 5:21 pm

well you really only "need" an association class if there is some other information you want stored with the association. say something like when the association was "created" (when the user was added to the role).

if you aren't using an associaiton class, you might do something like:

Code:

public IList GetUsersByRole(Role role) {
  session.CreateCriteria(typeof(User))
    .Add(Expression.Eq("Role", role))
    .List();
}

This assumes there is some sort of mapping between user object and role object.

HTH,
-devon

devonl · **Posted:** Tue May 31, 2005 5:31 pm

in answering the second question, i think i found a bug in the answer to the first. adding multiple criteria with the same name will produce an error, therefore i think it would have to be:

Code:

public IList GetItemsByCategory(Category cat) {
  return session.CreateCriteria(typeof(Item))
    .Add(Expression.Eq("Category", cat))
    .List();
}

public IList GetItemsByCategories(ArrayList cats) {
  return session.CreateCriteria(typeof(Item))
    .Add(Expression.In("Category", cats))
    .List();
}

haven't compiled the code but this should work better. sorry about that...

-devon

rolandz · **Posted:** Thu Jun 02, 2005 2:52 am

Hello again,

I'm sure that would work if I had an associacio class. However I would lik to do it without another class which is in fact some additional class that does not fit my design...

I have following mappings:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="Pix.Data.Category, Pix.Data" table="CATEGORY">
      <id name="Id" column="ID" unsaved-value="0" access="field">
         <generator class="hilo">
            <param name="table">HILO</param>
            <param name="column">CATEGORY</param>
            <param name="max_lo">100</param>
         </generator>
      </id>
      <property name="Name" column="CATEGORY_NAME" type="String(50)" access="field"/>
      <set name="Files" table="FILE_CATEGORY" lazy="true" inverse="true"
           access="field">
         <key column="CATEGORY_ID"/>
         <many-to-many class="Pix.Data.FileBase, Pix.Data" column="FILE_ID"/>
      </set>
   </class>
</hibernate-mapping>

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="Pix.Data.FileBase, Pix.Data" table="FILE_BASE">
      <id name="Id" column="ID" unsaved-value="0" access="field">
         <generator class="hilo">
            <param name="table">HILO</param>
            <param name="column">FILE_BASE</param>
            <param name="max_lo">100</param>
         </generator>
      </id>
      <property name="Name" column="FILE_NAME" type="String(200)" access="field"/>
      <property name="OriginalPath" column="ORIGINAL_PATH" type="String(300)"
                access="field"/>
      <set name="Categories" table="FILE_CATEGORY" lazy="true" access="field">
         <key column="FILE_ID"/>
         <many-to-many class="Pix.Data.Category, Pix.Data" column="CATEGORY_ID"/>
      </set>
   </class>
</hibernate-mapping>

These map C# classes to category and file tables in database. What I really want is to have a possibility to ask for a list of all files balonging to categories let's say A _and_ B, or A _or_ B and so on.

But w/o association in C# since there is already perfect one in mappings and database...

pixel_1001 · **Joined:** Tue Aug 18, 2009 11:39 am **Posts:** 1

hi, i add sentences to solution for devonl, because i get many items repeated whit the same id, and i use setResultTransformer() to get distinct.

Code:

public IList GetItemsByCategories(ArrayList cats) {
       Criteria criterio = null;
       sesion = getHibernateTemplate().getSessionFactory().getCurrentSession();
       //creamos criterio para la clase anteProyecto
       criterio = sesion.createCriteria(item.class);
       criterio.createCriteria("Category").add(Restrictions.in("id",cats);
       criterio.setResultTransformer(criterio.DISTINCT_ROOT_ENTITY);
       return criterio.list();
}

I hop it is useful to someone
Sorry for me inglish xD