AirDB Join Table Attributes

In some cases, it becomes necessary to have additional attributes associated with a many-many relationship.

This is typically true of “has_and_belongs_to_many” associations which end up mirroring some kind of “membership” between the joined models. For example, a library system might track borrowing of books by patrons, with extra attributes for return dates and accrued fines.

The Django guys have a handy example about musicians and bands with extra attributes such as the membership role and date of joining. In my case, I needed to track sharing status of Photos across Photosets.

AirDB now has support for such join table attributes. Its a bit of a hack, in the interests of time, code size and abstraction concessions. The new things, which make it all possible include:

1. An optional argument to Migrator.joinTable()
2. DB.execute(sql) to allow tweaking an existing join table.
3. Associator methods: setAttr, findAllByAttr, countByAttr, getAttrVal.

Here are some actual code excerpts (in the Photoset model)

   // The new migration directive
   function(my:Migrator):void {
      var statusCol:Array = ['status', DB.Field.VarChar, {
         limit: 4, 'default': Photo.ShareStatus.None}
      ];
      DB.execute(
         "ALTER TABLE photos_photosets ADD COLUMN " +
         DB.fieldMap(statusCol)
      )
   }

Setting the sharing status for a particular photo in this photoset

   public function photoSent(photo:Photo):void {
      this.photos.setAttr({status: Photo.ShareStatus.Sent}, photo);
   }

If no target is specified, the specified attributes are set for all associated photos in this photoset.

    this.photos.setAttr({status: Photo.ShareStatus.Sent});

Count the number of photos based on some attribute.

   var xfrCond:String = "status = '" + Photo.ShareStatus.Sent;
   this.photos.countByAttr(xfrCond);

Ideally, and with some coding leisure, this would be best done as a new has_many_through association. If you know of ActionScript language mechanisms to ease the implementation, I would be interested in hearing about them.

Meanwhile, we have functional join tables attributes.

This entry was posted on Friday, November 13th, 2009 at 4:43 pm and is filed under airdb, hacking. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

• PreviousjQuery(‘.contributors’).thanks();
• NextOn News