Maybe someone can fill in the details here, but the credit pair as tuple is something you'd do with association proxy. Model plain relationships from document to credit, then credit to role and person; then add an accessor to credit called "person_role_tuple", then the association proxy is on document and bridges document.credits -> person_role_tuple. See the association object pattern in the relationship docs and then follow the links to the "associationproxy" extension to continue.
Sent from my iPhone > On Apr 2, 2014, at 10:44 PM, Tony B <[email protected]> wrote: > > I'm quite new to SqlAlchemy, and I'm rolling right along with my project, > but I've hit a snag. I've been having trouble searching successfully for > this problem, but maybe I'm not using the right keywords. My apologies if > this is an oft repeated problem. > > I'm using SqlAlchemy 0.9 and SQLite, and trying to model credits for > documents (authorship, and such) > > Some details: > I have a model with 3 classes of objects/tables: Documents, Persons, and > Roles. > I want to associate Documents with pairs of Person-Roles (aka Credits). > Persons and Roles each have only an ID and a name. (Roles might be "editor", > "writer", "artist", etc) > A Person may fill zero or more Roles on a given document, > A particular Role may occur zero to many times on a document (i.e. multiple > writers). > A Person can have different Roles in different documents. (writer for one, > editor on another) > Just playing in SQLite, I created a three-way join table (called "credits"), > with a primary key from each of the three aforementioned tables. Somehow I > cobbled together a functional select statement that represents what I want: > > select documents.id, persons.name as person, roles.name as role > from documents > inner join credits > on credits.comic_id=documents.id > inner join persons > on credits.person_id=persons.id > inner join roles > on credits.role_id=roles.id > > I would like to somehow create a relationship for the Document class that > will give me the Credit pairs, as a python tuple or dict. > > Is this do-able? Or do I need to rethink my model? I'm a novice with > respect to some of this complex DB stuff, so any help would be appreciated! > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
