Thank you andrija and Micheal (especially recommending getting it
working first without associationproxy -- that really helped)
Here's a solution I've got that works (not exactly as I intended, but
I can live with it). I just keep the section and question paired
together.
from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
func
from sqlalchemy.orm import mapper, relation
from sqlalchemy.sql.expression import select, and_
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection
from datetime import datetime
from formencode import validators
from smmodels import NoHTML, SuperDateValidator
metadata = MetaData()
class SaqType:
def __init__(self):
pass
def __str__(self):
return 'id: %s name: %s' % (self.id, self.name)
def __repr__(self):
return "<Questionnaire Type(%s, name:'%s')>" % (self.id,
self.name)
class SaqJoin:
def __init__(self, type_obj=None, question_obj=None,
section_obj=None):
self.type = type_obj
self.question = question_obj
self.section = section_obj
def __repr__(self):
return '<SaqJoin type,question,section:(%s,%s,%s)>' %
(self.type_id, self.question_id, self.section_id)
class Questionnaire:
def __str__(self):
return 'id: %s customer_id: %s' % (self.id, self.customer_id)
def __repr__(self):
return "<Questionnaire(%s, customer_id:%s)>" % (self.id,
self.customer_id)
question_types = ['yn','yn_na','label','comment']
class Question:
pass
class Section:
def __init__(self):
pass
class Answer:
def __init__(self):
pass
class CustomerPart:
def __init__(self):
pass
sections_by_type = select(
[join_table.c.type_id, join_table.c.section_id],
group_by=[join_table.c.section_id,
join_table.c.type_id]).alias('sections_by_type')
mapper(Question, questions_table)
mapper(Section, sections_table)
mapper(CustomerPart, customer_table_part)
mapper(Answer, answers_table, properties={
'question' :relation(Question, backref='answer', uselist=False)
})
mapper(SaqJoin, join_table, properties={
'type' :relation(SaqType),
'section' :relation(Section, backref='parent',
order_by=join_table.c.ord),
'question' :relation(Question, backref='parent',
order_by=join_table.c.ord),
})
mapper(SaqType, types_table, order_by=types_table.c.id, properties={
'qs' :relation(SaqJoin, order_by=join_table.c.ord),
'my_sections' :relation(Section, secondary=sections_by_type,
primaryjoin = types_table.c.id == sections_by_type.c.type_id,
backref='type', order_by=sections_table.c.secnum),
})
mapper(Questionnaire, questionnaire_table, properties={
'answers' :relation(Answer, backref='questionnaire'),
'type' :relation(SaqType),
'customer' :relation(CustomerPart, backref='questionnaires')
})
On Jun 17, 9:26 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> any table that has more than just two foreign keys to remote tables
> does not normally qualify as a "secondary" table (its only allowed for
> certain edge cases which are not present here). The "Join" class
> mapped to the "join_table" is the right approach. In which case, you
> *definitely* don't want to be using "secondary" or "secondary_join" in
> any case here. Any access from A->(Join)->B where you don't want to
> "see" the Join object, you should use the associationproxy at that
> point - but get the entire thing to work first without using
> associationproxy, as its only a convenience extension.
>
> On Jun 16, 5:11 pm, Matt Haggard <[EMAIL PROTECTED]> wrote:
>
> > I've got a triple join table (joining three things together) and I'm
> > really struggling to get it to work as I intend. I've pasted the full
> > model at the bottom. I've struggled with this off and on for months
> > now... I don't really understand how I can get SQLAlchemy to do what I
> > want.
>
> > The join table has 4 columns:
>
> > type_id | section_id | question_id | ord
>
> > What I'd like is the following:
>
> > Questionnaire object with:
> > .sections list that contain
> > .questions list that contain:
> > .answer
>
> > So, something like:
> > q = Questionnaire()
> > print q.sections[2].questions[1].answer
> > # yield the answer to question 1 of section 2 (or question 2 of
> > section 3 depending on your indexing :) )
>
> > I encounter a problem because a Question object doesn't know what the
> > type_id is because that is stored with the Questionnaire object.
> > Likewise from a section object. I'm pulling my hair out...
>
> > Thanks,
>
> > Matt Haggard
>
> > --------------------------------------------
> > from sqlalchemy import Column, MetaData, Table, types, ForeignKey,
> > func
> > from sqlalchemy.orm import mapper, relation
> > from sqlalchemy.sql.expression import select
> > from sqlalchemy.ext.associationproxy import association_proxy
>
> > from datetime import datetime
>
> > from formencode import validators
> > from smmodels import NoHTML, SuperDateValidator
>
> > metadata = MetaData()
>
> > types_table = Table('q_type', metadata,
> > Column('id', types.Integer, primary_key=True, index=True),
> > Column('name', types.Unicode),
> > Column('longname', types.Unicode),
> > Column('validation_type', types.Unicode)
> > )
>
> > customer_table_part = Table('customer', metadata,
> > Column('id', types.Integer, primary_key=True, index=True),
> > Column('email', types.Unicode, unique=True, index=True),
> > Column('validation_type', types.Unicode)
> > )
>
> > questionnaire_table = Table('q_questionnaire', metadata,
> > Column('id', types.Integer, primary_key=True, index=True),
> > Column('type_id', types.Integer, ForeignKey('q_type.id')),
> > Column('started', types.DateTime, default=datetime.now),
> > Column('firstpage_firstdone', types.DateTime),
> > Column('firstpassed', types.DateTime),
> > Column('customer_id', types.Integer, ForeignKey('customer.id'),
> > index=True),
> > Column('q_busdescrip', types.Unicode),
> > Column('q_dsedescrip', types.Unicode),
> > Column('q_providedallip', types.Unicode),
> > Column('q_company_name', types.Unicode),
> > Column('q_dba', types.Unicode),
> > Column('q_contact_name', types.Unicode),
> > Column('q_phone', types.Unicode),
> > Column('q_trans_num_yearly', types.Unicode),
> > Column('q_title', types.Unicode),
> > Column('grade', types.Unicode),
> > Column('failing_sections', types.Integer, default=0),
> > Column('incomplete_sections', types.Integer, default=0),
> > Column('number_blacklisted', types.Integer, default=0),
> > Column('lastupdated', types.DateTime, default=datetime.now),
> > Column('expiration_date', types.DateTime)
> > )
>
> > questions_table = Table('q_questions_new', metadata,
> > Column('id', types.Integer, primary_key=True, index=True),
> > Column('display_number', types.Unicode),
> > Column('question', types.Unicode),
> > Column('type', types.Unicode),
> > Column('weight', types.Integer),
> > Column('correct_answer', types.Unicode, default=''),
> > Column('dft_order', types.Integer)
> > )
>
> > sections_table = Table('q_sections_new', metadata,
> > Column('id', types.Integer, primary_key=True, index=True),
> > Column('secnum', types.Unicode),
> > Column('name', types.Unicode),
> > Column('longname', types.Unicode)
> > )
>
> > join_table = Table('q_join', metadata,
> > Column('type_id', types.Integer, ForeignKey('q_type.id'),
> > index=True, primary_key=True),
> > Column('question_id', types.Integer,
> > ForeignKey('q_questions_new.id'), index=True, primary_key=True),
> > Column('section_id', types.Integer,
> > ForeignKey('q_sections_new.id'), index=True, primary_key=True),
> > Column('ord', types.Integer)
> > )
>
> > answers_table = Table('q_answers_new', metadata,
> > Column('id', types.Integer, primary_key=True, index=True),
> > Column('question_id', types.Integer,
> > ForeignKey('q_questions_new.id'), index=True),
> > Column('q_id', types.Integer, ForeignKey('q_questionnaire.id'),
> > index=True),
> > Column('answer', types.Unicode),
> > Column('answerdate', types.DateTime, default=datetime.now),
> > Column('markedby', types.Unicode, default='hand'),
> > Column('visible', types.Boolean, default=True)
> > )
>
> > class qType:
>
> > allquestions = association_proxy('joinObj', 'questions')
> > sections = association_proxy('joinObj', 'sections')
>
> > def __init__(self):
> > pass
>
> > def __str__(self):
> > return 'id: %s name: %s' % (self.id, self.name)
>
> > def __repr__(self):
> > return "<Questionnaire Type(%s, name:'%s')>" % (self.id,
> > self.name)
>
> > class qJoin:
>
> > def __init__(self, type_obj=None, question_obj=None,
> > section_obj=None):
> > self.type = type_obj
> > self.question = question_obj
> > self.section = section_obj
>
> > def __repr__(self):
> > return '<qJoin type,question,section:(%s,%s,%s)>' %
> > (self.type_id, self.question_id, self.section_id)
>
> > class Questionnaire:
>
> > def __str__(self):
> > return 'id: %s customer_id: %s' % (self.id, self.customer_id)
>
> > def __repr__(self):
> > return "<Questionnaire(%s, customer_id:%s)>" % (self.id,
> > self.customer_id)
>
> > question_types = ['yn','yn_na','label','comment']
> > class Question:
>
> > #section = association_proxy('joinObj', 'section')
>
> > class Section:
>
> > def __init__(self):
> > pass
>
> > def getQuestions(self, type_id):
> > '''Query for questions belonging to both this section and
> > type_id'''
> > q = select([join_table.c.question_id,
> > join_table.c.ord],
> > whereclause=[join_table.c.type_id==type_id,join_table.c.section_id==self.id])
> > return q
>
> > class Answer:
>
> > def __init__(self):
> > pass
>
> > class CustomerPart:
>
> > def __init__(self):
> > pass
>
> > sections_by_type = select(
> > [join_table.c.type_id, join_table.c.section_id],
> > group_by=[join_table.c.section_id,
> > join_table.c.type_id]).alias('sections_by_type')
>
> > questions_sections_by_tyoe = select(
> > [join_table.c.type_id, join_table.c.question_id,
> > join_table.c.section_id],
> > group_by=[join_table.c.question_id, join_table.c.section_id,
> > join_table.c.type_id]).alias('q_s_by_type')
>
> > mapper(Question, questions_table)
> > mapper(Section, sections_table, properties={
> > 'questions' :relation(Question, secondary=join_table,
> > primaryjoin = sections_table.c.id == join_table.c.section_id,
> > secondaryjoin = join_table.c.question_id ==
> > questions_table.c.id,
> > backref='section', order_by=join_table.c.ord),})
>
> > mapper(CustomerPart, customer_table_part)
> > mapper(Answer, answers_table, properties={
> > 'question' :relation(Question, backref='answer', uselist=False)})
>
> > mapper(Join, join_table, properties={
> > 'type' :relation(qType),
> > 'sections' :relation(Section, backref='parent'),
> > 'questions' :relation(Question, backref='parent'),})
>
> > mapper(qType, types_table, order_by=types_table.c.id, properties={
> > 'joinObj' :relation(qJoin),
> > 'my_sections' :relation(Section, secondary=sections_by_type,
> > primaryjoin = types_table.c.id == sections_by_type.c.type_id,
> > backref='type', order_by=sections_table.c.secnum),})
>
> > mapper(Questionnaire, questionnaire_table, properties={
> > 'answers' :relation(Answer, backref='questionnaire'),
> > 'type' :relation(qType),
> > 'customer' :relation(CustomerPart, backref='questionnaires')
>
> > })
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
