-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: many-to-many query woes
PostPosted: 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
 Profile  
 
 Post subject:
PostPosted: 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
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.