Relations
=========

Relations also need to be managed by a workflow system. Clio knows how
to handle objects that have relations, if at least we define these
relations in a special manner.

One to Many Relations
---------------------

Normally when creating a relation using the SQLAlchemy ORM, you use
the ``relation`` constructor in the mapper. With Clio you have to use
``dynamic_loader`` instead. This is also supplied by SQLAlchemy and
receives the same parameters as ``relation``. The difference is that
``dynamic_loader`` easily allows further filtering to be done on the
generated relation properties, which Clio uses to set up its
workflow-aware properties. ``dynamic_loader`` generates properties
that are queries, not true collections.

We define two Clio tables with a foreign key relation between them::

  jar = clio.Table('jar', metadata,
     Column(name, Unicode(20), nullable=False))

  cookie = clio.Table('cookie', metadata,
     Column(name, Unicode(20), nullable=False),
     Column('jar_id', Integer, ForeignKey('jar.id'), nullable=False))

We can now set up two models that represent these tables::
 
  class Jar(clio.Model):
      def __init__(self, code, name):
          super(Jar, self).__init__(code)
          self.name = name

  class Cookie(clio.Model):
      def __init__(self, code, name):
          super(Cookie, self).__init__(code)
          self.name = name

We map the classes to the tables and define a relation::

  mapper(Jar, jar,
         properties={
           'cookies': dynamic_loader(Cookie, backref('jar')),
         })

``Jar`` instances will now have a ``cookies`` property on it. This
property is not a collection like you would get if you had used
``relation``, but a SQLAlchemy query object. You can loop through it
and access individual items like you would with a Python ``list``, but
other operations (like ``len``) won't work. To treat it as a list, you
can however always simply use ``list`` on it::

  list(somejar.cookies)

This executes the query and puts the resulting objects in a list.

Workflow properties
-------------------

The ``cookies`` property represents *all* cookies that are in the jar,
new, published or archived. Clio provides other relation properties
that give just the published relations, the archived relations or the
newly created or under edit relations (or the under edit relations and
those relations that were deleted). To establish those properties, you
need to call ``clio.workflow_properties`` on the mapped classes::

  clio.workflow_properties(Jar)
  clio.workflow_properties(Cookie)

Now you can access the following extra properties::

  somejar.cookies_archived
  somejar.cookies_published
  somejar.cookies_editable
  somejar.cookies_actual

A special ``cookies_original`` relation also exists. This is because
the ``cookies`` relation has special behavior for ``UPDATABLE``
versions (it refers back to the published version to retrieve relation
information).

Many to Many Relations
----------------------

The other major type of relation that Clio supports is the
many-to-many relation. In this case a pivot (or secondary) table
exists that describes a many to many relationship between two other
tables::

  bird = clio.Table('bird', metadata,
     Column(name, Unicode(20), nullable=False))

  feeding_site = clio.Table('feeding_site', metadata,
     Column(name, Unicode(20), nullable=False))

  from sqlalchemy import Table

  # the secondary table
  bird_feeding_site = Table('bird_feeding_site', metadata,
     Column('bird_id', Integer, ForeignKey('bird.id'), nullable=False),
     Column('feeding_site_id', Integer, ForeignKey('feeding_site.id'), nullable=False))

Note that the pivot table is not defined as a Clio table but is
instead a normal SQLALchemy table.

Next, we set up the classes to be mapped by the ORM::

  class Bird(clio.Model):
      def __init__(self, code, name):
          super(Bird, self).__init__(code)
          self.name = name

  class FeedingSite(clio.Model):
      def __init__(self, code, name):
          super(FeedingSite, self).__init__(code)
          self.name = name
   
And we set up the mapping itself::

  mapper(Bird, bird)

  mapper(FeedingSite, feeding_site,
         properties={
           'birds': dynamic_loader(
              Bird,
              secondary=bird_feeding_site,
              backref=backref('feeding_sites', lazy='dynamic')),
          })

Again, we use ``dynamic_loader``. This time we also need to supply a
special parameter to the ``backref``, namely ``lazy='dynamic'``. This
is to ensure that the ``feeding_sites`` properties generated on the
``Bird`` class is also a ``dynamic_loader`` relation.

Finally we need to set up the workflow properties::

  clio.workflow_properties(Bird)
  clio.workflow_properties(FeedingSite)