Hibernate Community • View topic - many-to-many query woes

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

many-to-many query woes

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

donaldh

Post subject: many-to-many query woes

Posted: Sun Jul 24, 2005 3:53 pm

Newbie

Joined: Sun Jul 24, 2005 3:33 pm
Posts: 2

Hibernate version: 2

Mapping documents:

Code:

    <class name="User" table="user">
        <id name="userId" type="int" column="user_id" unsaved-value="0">
            <generator class="identity" />
        </id>
        
        <bag name="feeds" table="user_feed" lazy="true" cascade="save-update">
            <key column="user_id" />
            <many-to-many class="AbstractFeed" column="feed_id" />
        </bag>
    </class>

The AbstractFeed class has no defined relationship to user.

Query (in HSQL):

Code:

select count(*) from AbstractFeed feed, User user
where feed in elements( user.feedsAsList )
and user.userId = 4
and feed.feedId = 30

Name and version of the database you are using: MySQL 4

The generated SQL (show_sql=true):

Code:

select count(*) as x0_0_
from feed abstractfe0_, user user1_
where ( abstractfe0_.feed_id in (
   select feedsaslis2_.feed_id
   from user_feed feedsaslis2_
   where user1_.user_id=feedsaslis2_.user_id ) )
and ( user1_.user_id = 4 )
and ( abstractfe0_.feed_id = 37 )

Problem:
I'm trying to determine if a relationship exists between a user and a feed, and thus far the above HSQL is as close as I've been able to come. In fact the query would work if MySQL supported correlated subqueries, but it does not. Is there any way that I can re-write the query to get aroud this?

Thanks for reading!
- Donald

Top

donaldh

Post subject:

Posted: Sun Jul 24, 2005 11:47 pm

Newbie

Joined: Sun Jul 24, 2005 3:33 pm
Posts: 2

I solved my problem by introducing a class that represents the join table. It's not perfect, but functional.

New Class:

Code:

public class UserFeed {

    private int userFeedId;
    private User user;
    private AbstractFeed feed;
    // getters and setters for above fields
}

Mapping Document:

Code:

    <class name="UserFeed" table="user_feed">
        <id name="userFeedId" type="int" column="user_feed_id" unsaved-value="0">
            <generator class="identity" />
        </id>
        
        <many-to-one name="user" class="User" column="user_id" />
        <many-to-one name="feed" class="AbstractFeed" column="feed_id" />
    </class>

Query (in HSQL):

Code:

select count(*)
from UserFeed userFeed
where userFeed.user.userId = 4
and userFeed.feed.feedId = 38

The generated SQL:

Code:

select count(*) as x0_0_
from user_feed userfeed0_
where ( userfeed0_.user_id=4 )
and ( userfeed0_.feed_id=38 )

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum