many-to-many - again :-(

Mateusz [PEYN] Adamus · **Posted:** Fri Aug 05, 2005 3:51 pm

Hi

I looked on forum, google and documentation. Found some examples but I still can't get it to work :-(

I have these tables:

files
(
fil_id
fil_name
)

folders
(
fol_id
fol_name
)

files_in_folders
(
fif_fil_id
fif_fol_id
)

As you can see its many-to-many realtion.

My mapping look like this:

Files table

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="WindowsApplication1.Files, WindowsApplication1" table="`files`">
      <id name="Id" column="fil_id"> 
         <generator class="sequence"> 
            <param name="sequence">files_fil_id_seq</param>
         </generator>
      </id> 
      <property name="Name" column="fil_name" /> 
      <bag name="Folders" table="`files_in_folders`" cascade="save-update" >
         <key column="fil_id" />
         <many-to-many column="fif_fol_id" class="WindowsApplication1.Folders,WindowsApplication1" />
      </bag>       
   </class>
</hibernate-mapping>

Folders

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="WindowsApplication1.Folders, WindowsApplication1" table="`folders`">
      <id name="Id" column="fol_id"> 
         <generator class="sequence"> 
            <param name="sequence">folders_fol_id_seq</param>
         </generator>
      </id> 
      <property name="Name" column="fol_name" /> 
      <property name="Description" column="fol_description" /> 
      <bag name="Files" table="`files_in_folders`" cascade="save-update" >
         <key column="fol_id" />
         <many-to-many column="fif_fol_id" class="WindowsApplication1.Files,WindowsApplication1" />
      </bag>       
   </class>
</hibernate-mapping>

OK. Mapping are ok - I can create Configuration object. But when I try to get file with id = 1 query looks like this

Code:

SELECT *
FROM files_in_folders fif 
INNER JOIN folders fol ON fif.fil_id=fol.fol_id
WHERE fif.fil_id = 1

Ofcourse its wrong - there is no field fil_id in files_in_folders table.

What is wrong with these mappings? Maybe I should use many-to-one mapping instead?

And what if I would like to add some extra field in files_in_folders for storing some extra data - would many-to-many relation be ok for getting this extra data out?

Many thanks in advance

best regards
Mateusz [PEYN] Adamus

realien · **Posted:** Sat Aug 06, 2005 12:10 am

Why even have the folders.hbm.xml or folder.hbm.xml aware of each other...

if you create the file_folders table to have either a composite key or its own key..

e.g. psuedo code...

FilesFoldersJoin

int id
int fileId
int folderId

and in the database create a constraint so that both fileId and folderId must be a foreign key to the primary keys on those tables so you don't get an invalid record in the files/folders join table..

now your mapping is :

Files

Code:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="WindowsApplication1.Files, WindowsApplication1" table="`files`">
      <id name="Id" column="fil_id">
         <generator class="sequence">
            <param name="sequence">files_fil_id_seq</param>
         </generator>
      </id>
      <property name="Name" column="fil_name" />
 <property name="Description" column="fil_description" />
       
   </class>
</hibernate-mapping> 

Folders:

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="WindowsApplication1.Folders, WindowsApplication1" table="`folders`">
      <id name="Id" column="fol_id">
         <generator class="sequence">
            <param name="sequence">folders_fol_id_seq</param>
         </generator>
      </id>
      <property name="Name" column="fol_name" />
      <property name="Description" column="fol_description" />
   </hibernate-mapping> 

Code:

<?xml version="1.0" encoding="utf-8" ?>

New FileFolderJoin Object...

Code:

class FileFolderJoin
{
  int id;;
  Folder folder;
  File file;
}

Files/Folder mapping for your new Files/Folder Object...

Code:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
   <class name="WindowsApplication1.FilesFoldersJoin, WindowsApplication1" table="`folders`">
      <id name="Id" column="fol_id">
         <generator class="sequence">
            <param name="sequence">folders_fol_id_seq</param>
         </generator>
      </id>
      <many-to-one name="File" class="WindowsApplication1.File" column="fileId"/>
 <many-to-one name="Folder" class="WindowsApplication1.Folder" column="FolderId"/>
     

</hibernate-mapping> 

Code:

<?xml version="1.0" encoding="utf-8" ?>

now you can do a query

select FileFolder as ff where ff.File.Id =1234;

So you'll get all fileFolders Object that contains both the file and the folder objects

you an also do

select FileFolder as ff where ff.Folder.Id = 12345

Hibernate in action is a good book, its written for the java version but it does give a lot of ideas on how to structure your objects. Many to many is not good and I find using join tables and objects gives me better performance and simpler mapping files.

PEYN · **Posted:** Sun Aug 07, 2005 12:23 pm

realien wrote:

Why even have the folders.hbm.xml or folder.hbm.xml aware of each other...

I'd like to do a list of files. When user click on that list I would like to show him in what folders is file which he clicked on.

I'm really new to nhibernate - its my 3rd day plating with it - so probably what I want to do can be done in much simpler way (like you said - without any awareness).

realien wrote:

if you create the file_folders table to have either a composite key or its own key..

e.g. psuedo code...

FilesFoldersJoin

int id
int fileId
int folderId

and in the database create a constraint so that both fileId and folderId must be a foreign key to the primary keys on those tables so you don't get an invalid record in the files/folders join table..

I have constraint in db

realien wrote:

Hibernate in action is a good book, its written for the java version but it does give a lot of ideas on how to structure your objects. Many to many is not good and I find using join tables and objects gives me better performance and simpler mapping files.

Thanks for the book - I'll look for it