How to write that query?

wojski696969 · **Joined:** Mon Aug 11, 2008 3:17 pm **Posts:** 6

Hi..

I have 2 classes, Player and Team. Team has a collection of players, and my problem is to write query wich will select from my database all Players wich has no team, I mean players wich are not in any team.

It could be easy if I use classic SQL, because TeamId is a field in Players table, but how can i get whole player object using classic SQL? Or wich hibernate query will return me list of this player objects?

My mapping for those classes:

Code:

<class name="Player" table="Players">

    <id name="Id" column="Id" type="String">
      <generator class="uuid.hex" />
    </id>

    <property name="FirstName" type="String" length="40">
      <column name="FirstName" />
    </property>

    <property name="LastName" type="String" length="40">
      <column name="LastName" />
    </property>

    <many-to-one name="UserID" class="User" cascade="all"  lazy="false" column="UserID"/>   

  </class>

  <class name="Team" table="Teams">

    <id name="Id" column="Id" type="String">
      <generator class="uuid.hex" />
    </id>


    <property name="Name" type="String" length="150">
      <column name="Name" />
    </property>

    <bag name="Players" cascade="all" lazy="false">
      <key column="TeamId" />
      <one-to-many class="Player" />
    </bag>

  </class>

I hope that someone can help me..
Thanks a lot...

Nandem · **Posted:** Mon Aug 11, 2008 4:40 pm

Make a bidirectional property anchor in a "Player" class that doesn't cascade anything...
hmm, something like this:

Code:

<many-to-one name="Team" column="TeamId" class="Team" cascade="none" not-null="false" />

so, when u load list of "Teams", in each have an list of "Players" that will have an object reference do "Team". i gess u get it...

and, in hql, if u want to list a "Players" without a team. try that:

Code:

from Player player where player.Team is null

Rapid tip: Try not use cascade="all" in "child" classes, because, the hibernate will generate "update" statments to update your "father" classes....

check it <many-to-one name="UserID" class="User" cascade="all" lazy="false" column="UserID"/>

use cascade="none" if the intention is only relationship, not commit all "relation" data... =)

cya

Nandem · **Posted:** Mon Aug 11, 2008 4:58 pm

Ups, double posted!! ^^

wojski696969 · **Joined:** Mon Aug 11, 2008 3:17 pm **Posts:** 6

Nice.. i thought about that solution, but is there no posibility to make it in just one query without extra properties?

wolli · **Posted:** Tue Aug 12, 2008 2:24 am

Try this:

Code:

from Player p where not exist (select pls.Id from Team t join t.Players pls where p.Id = pls.Id)

wojski696969 · **Joined:** Mon Aug 11, 2008 3:17 pm **Posts:** 6

Sorry, but..

Quote:

Incorrect query syntax [from Entities.Player p where not exist (select pls.Id from Entities.Team t join t.Players pls where p.Id = pls.Id)]

Any ideas?

--------

Ohh.. I've change it to

Quote:

select p from Player p where p not in (select pls.Id from Team t join t.Players pls where p.Id = pls.Id)

and now it works correctly ;)

Thanx for your help..

wolli · **Posted:** Tue Aug 12, 2008 8:44 am

"exists" not "exist" .. was already misspelled in my original post.