count with many-to-many

naknak · **Joined:** Wed Feb 25, 2004 1:31 am **Posts:** 2

Hi there. I'm having what I hope is a very simple problem, but
I can't figure out the right hql syntax.

I'm trying to use count in a many-to-many relationship.
The problem is I can't figure out the right HQL syntax to name
the mapping table.

I have a many-to-many relationship between Companies and Categories.
There is the relevent set defintion:

Code:

    <set name="categories" lazy="true" table="category_company" cascade="all" >

      <key column="company_id" />
      <many-to-many column="category_id" class="Category"
        outer-join="true"
        >
        <column name="category_id" index="in_category_company_category" />

      </many-to-many>
    </set>

I'm using the automatic schema generator in hbm2ddl; it generated this mapping table:

Code:

create table category_company (
   company_id BIGINT not null,
   category_id BIGINT not null,
   primary key (company_id, category_id)
);

So far, so good.

I'm trying to count how many companies are in each category. This
sql does what I want, when run directly against the database:

Code:

    select count(*), cc.category_id
    from company c, category_company cc
    where c.id = cc.company_id
    group by cc.category_id;

I tried running this find code:

Code:

list = session.find("select count(*), cc.category_id from Company c, company_category cc where c.id = cc.company_id group by cc.category_id");

I get a QueryException: in expected: cc

From the code it seems to want an "in" token instead of cc,
so I'm obvously confused as to the proper syntax.

So, here's my question: how do I name the mapping
table for doing HQL operations?

Thanks in advance...

(This is using hibernate 2.1.2, and mysql 4.0.16)

emmanuel · **Posted:** Wed Feb 25, 2004 8:35 am

Code:

select count(*), cc.category_id from Company join c.categories cc group by cc.id

Something like that probably

naknak · **Joined:** Wed Feb 25, 2004 1:31 am **Posts:** 2

Thanks for the clue! It definitely helped.

The code that actually got the right results (just in case someone comes
along later and wants to copy the idea) was:

Code:

select count(*), cc.id
from Company c join c.categories cc group by cc.id

This gives the right answer, but ends up joining through all three
tables (category, company, and the category_company mapping
table). Here's the SQL that got run:

Code:

Hibernate: select count(*) as x0_0_, category2_.id as x1_0_ from company company0_ inner join category_company categories1_ on company0_.id=categories1_.company_id inner join category category2_ on categories1_.category_id=category2_.id group by  category2_.id

So my immediate need is met -- I'm getting the right answer (thanks again!).
But I'm worried about gettling flack from some DBA to be named later that I'm joining an extra table. Perhaps there is no way to name the mapping table, but if there was I would use it.