[storm] Many-to-many with join data

[James Henstridge]

On Sat, Oct 30, 2010 at 5:49 AM, Matt Haggard <haggardii at gmail.com> wrote:
> I have three tables:
>
> Company { id, name }
> CompanyModelJoin { id, company_id, model_id, tag }
> Model { id, name }
>
> CompanyModelJoin is the many-to-many join table.
>
> So, a company can have multiple models and a model can be used by
> multiple companies.  But there's that tag field in the join table.  I
> want somehow to do:
>
>>>> print '\n'.join(['%r, %r' %x for x in company.models])
> 'tag-foo', <Model object A>
> 'tag-bar', <Model object B>
> 'another tag', <Model object A>
>
> ============================================================================
> The Question
>
> How would I construct the .models attribute on the Company class to do
> something like that?  Or should I just make custom getter/setter
> functions?
> ============================================================================
>
> If this is too much of database-design question and not specific to
> storm, I can ask elsewhere.

You Can generate a query that acts like that with:

    @property
    def models(self):
        return Store.of(self).find((CompanyModelJoin.tag, Model),
            CompanyModelJoin.company_id == self.id,
            CompanyModelJoin.model_id == Model.id)

This will give you a ResultSet though, which doesn't have the
convenience methods for adding and removing related objects as you
would get with a ReferenceSet.  If you are happy creating the
CompanyModelJoin records manually, then this shouldn't be a problem
though.

James.