weird behaviour possibly from my knowledge or sqlalchemy or turbogears
<p>Suppose I got two models. Account and Question.</p>

<pre><code>class Account(DeclarativeBase):
    __tablename__ = 'accounts'

    id = Column(Integer, primary_key=True)
    user_name = Column(Unicode(255), unique=True, nullable=False)
</code></pre>

<p>and my Question model be like:</p>

<pre><code>class Question(DeclarativeBase):
    __tablename__ = 'questions'

    id = Column(Integer, primary_key=True)
    content = Column(Unicode(2500), nullable=False)
    account_id = Column(Integer, ForeignKey(
    'accounts.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False)
    account = relationship('Account', backref=backref('questions'))
</code></pre>

<p>I got a method that returns a question in json format from the provided question ID.
when the method is like this below, it only returns the <code>id</code> the <code>content</code> and the <code>account_id</code> of the question.</p>

<pre><code>    @expose('json')
    def question(self, question_id):
        return dict(questions=DBSession.query(Question).filter(Question.id == question_id).one())
</code></pre>

<p>but I need the user_name of Account to be included in the json response too. something weird (at least to me) is that I have to explicitly tell the method that the query result contains a relation to an Account and this way the account info will be included in the json response: I mean doing something like this</p>

<pre><code>    @expose('json')
    def question(self, question_id):
        result = DBSession.query(Question).filter(Question.id == question_id).one()
        weird_variable = result.account.user_name
        return dict(question=result)
</code></pre>

<p>why do I have to do such thing? what is the reason behind this?</p>

<p>From <a href="http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html" rel="nofollow">Relationship Loading Techniques</a>:</p>

<blockquote>
  <p>By default, all inter-object relationships are <strong>lazy loading</strong>.</p>
</blockquote>

<p>In other words in its default configuration the relationship <em>account</em> does not actually load the account data when you fetch a <code>Question</code>, but when you access the <code>account</code> attribute of a <code>Question</code> instance. This behaviour can be controlled:</p>

<pre><code>from sqlalchemy.orm import joinedload

DBSession.query(Question).\
    filter(Question.id == question_id).\
    options(joinedload('account')).\
    one()
</code></pre>

<p>Adding the <code>joinedload</code> option instructs the query to load the relationship at the same time with the parent, using a join. Other eager loading techniques are also available, their possible use cases and tradeoffs discussed under <a href="http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#what-kind-of-loading-to-use" rel="nofollow">"What Kind of Loading to Use?"</a></p>

python sqlalchemy turbogears2