Question How to create a one-to-many relationship in XWiki?
Answer Many developers come with a relational database formation, thinking in tables and relations between them. While the default XWiki storage has a RDBMS for the backend, the abstract data inside XWiki is actually an object hierarchy: a set of documents, which have objects, which have properties. For more details, see the XWiki data model. There are no hard links between these entities, but like in a RDB, they have identifiers which are used to establish relations between them. With the help of these identifiers and the XWiki data model, new relations can be created between documents.

How to create a one-to-many relation: Let's assume you have different persons and projects, and you want to specify for each person the projects on which he worked or is working on.

  1. Create a new class which will hold the relation. If your application already has a main class for its datatype, you can use that one instead. Let's call it My.PersonClass.
  2. Add a property of type Database List (DBListClass) to the class, called projects.
  3. Check the Multiple Select option, and optionally:
    • check the Relational Storage option if there will be persons with many projects or if you want to easily use this information in other queries
    • choose the Display Type
    • if the display type is select, also change the Size option to a larger value (this specifies the number of rows visible in the select list)
    • define the Join Separator (", " is a good option)
  4. Define the query that will populate the list. This can be done in two ways:
    1. Using the XWiki Class Name, Id Field Name and Value Field Name properties:
      1. XWiki Class Name should be the name of the target class, in this case the class for projects, let's say My.ProjectClass
      2. Id Field Name should be the "foreign key" that will be stored in the database. Assuming that each project is stored in its own page, the name of the document holding the project object is a good choice: doc.fullName
      3. Value Field Name is the value that will be displayed to the user in the selection list, so the name of the project should be a good candidate. If it is stored as the document title, use doc.title, and if it is stored in a property of the project class, use the name of that property, for example projectName
    2. Using the HQL query:
      1. While the previous approach is much simpler, a direct query gives more flexibility. Unlike the public $xwiki.searchDocuments method, a full query is expected here, starting with the SELECT. There is no standard query which can be used for all usecases, because of the flexibility of HQL. The only rule is that the query should select one (if the value and ID fields should be the same) or two (if different value and ID fields should be used) columns.
      2. A good example, doing what the previous approach does, using the projectName property as the value field, plus filtering out the project template document: SELECT obj.name, projectNameProperty.value FROM BaseObject obj, StringProperty projectNameProperty WHERE obj.className = 'My.ProjectClass' AND projectNameProperty.id.id = obj.id AND projectNameProperty.id.name = 'projectName' AND obj.name <> 'My.ProjectTemplate'
      3. Using the document title as the value field: SELECT doc.fullName, doc.title FROM XWikiDocument doc, BaseObject obj WHERE obj.className = 'My.ProjectClass' AND obj.name = doc.fullName AND obj.name <> 'My.ProjectTemplate'
  5. Display the list of projects in the person sheet (My.PersonSheet): $doc.use('My.PersonClass') $doc.display('projects'). This will display a comma-separated list of project names in view mode, and a list of project to choose from in edit mode (of course, as customized by the join separator and display type properties). Note that using the Class Wizard to create the document sheet will already add the right code, so there's no need to manually add it afterwards.
  6. Display the list of persons working on a project in the project sheet (My.ProjectSheet):
#foreach($personDoc in $xwiki.wrapDocs($xwiki.searchDocuments(
    ", BaseObject obj, DBStringListProperty as projects join projects.list as project WHERE
    obj.name = doc.fullName AND projects.id.id = obj.id AND projects.id.name = 'projects'
    AND project = '${doc.fullName}'")))
  * $personDoc.title
  * or: $personDoc.use('My.PersonClass') $personDoc.display('personName', 'view')
#end

It is important to remember that the projects property does not hold real project objects, or project documents, but just the name of the documents holding the projects. To access the project objects, do something like:

#foreach($projectDoc in
    $xwiki.wrapDocs($doc.getObject('My.PersonClass').getProperty('projects').value))
  ## $projectDoc is now the document holding the project
  #set($projectObj = $projectDoc.getObject('My.ProjectClass'))
  ## $projectObj is now the xobject holding the project
  Do stuff with $projectDoc and $projectObj
#end
Tags:
Created by Sergiu Dumitriu on 2009/12/19
   

Get Connected