title: SQLAlchemy select with all many to many relationship objects as list
body: <p>Given this Many to Many relationship:</p>
  
  <pre><code>tagmap = db.Table('tagmap', db.Model.metadata,
      db.Column('post_id', db.Integer, db.ForeignKey('posts.id'),
      db.Column('tag_id', db.Integer, db.ForeignKey('tags.id'),)
  
  class Post(db.Model):
      __tablename__ = 'posts'
      id    = db.Column(db.Integer, primary_key=True)
      text  = db.Column(db.Text)
      tags  = db.relationship('Tag', secondary=tagmap, backref='posts')
  
  class Tag(db.Model):
      __tablename__ = 'tags'
      id    = db.Column(db.Integer, primary_key=True)
      name  = db.Column(db.String)
  </code></pre>
  
  <p>How would you construct a query to select all the Posts including an iterable list of associated Tag objects?</p>
  
  <p>Desired query result structure:</p>
  
  <pre><code>[ &lt;Post 1, "Blah",      [&lt;Tag "Goober"&gt;, &lt;Tag "Mexico"&gt;]&gt;,
    &lt;Post 2, "Daft Punk", [&lt;Tag "French"&gt;]&gt;,
    &lt;Post 3, "Slurpee",   [&lt;Tag "Diabetes"&gt;, &lt;Tag "Tasty"&gt;, &lt;Tag "Sugary"&gt;]&gt;,
    &lt;Post 4, "Lasers",    []&gt; ]
  </code></pre>
  
  <p>Example desired result usage in a Jinja template:</p>
  
  <pre><code>{% for post in posts %}
      {{ post.text }}
      &lt;hr&gt;
      {% for tag in post.tags %}
          {{ tag.name }}, 
      {% endfor %}
  {% endfor %}
  </code></pre>
  
  <p><strong>Edit</strong>:</p>
  
  <p>When trying to extend the query, the tags stop being included. See failed query below:</p>
  
  <pre><code>Post.query.join(User)\
      .options(db.joinedload(Post.tags))\
      .order_by(Post.create_date.desc()).limit(5)\
      .values(User.name.label('author'),
              Post.create_date,
              Post.text,)
  </code></pre>
  
  <p><strong>Edit 2</strong>:</p>
  
  <p>So this query works great:</p>
  
  <pre><code>posts = db.session.query(Post, User.name.label('author')).join(User)\
              .options(db.joinedload(Post.tags))\
              .order_by(Post.created.desc()).limit(5)
  </code></pre>
  
  <p>but if I want to be selective about the columns in Post I select:</p>
  
  <pre><code># I only need the post text, nothing else
  posts = db.session.query(Post.text, User.name.label('author')).join(User)\
              .options(db.joinedload(Post.tags))\
              .order_by(Post.created.desc()).limit(5)
  </code></pre>
  
  <p>It begins to fall apart quickly:</p>
  
  <pre><code>ArgumentError: Query has only expression-based entities - can't find property named 'tags'.
  </code></pre>
  
  <p>So I try to add the tags back:</p>
  
  <pre><code># Yes, I know this is wrong
  posts = db.session.query(Post.text, Post.tags, User.name.label('author'))\
              .join(User)\               
              .options(db.joinedload(Post.tags))\
              .order_by(Post.created.desc()).limit(5)
  </code></pre>
  
  <p>Which still fails.</p>
  
  <p>So if you only want specific columns from the parent object (Post), where to you say in the expression, "I still want the tags"?</p>
  
  <p>It's not really that important now, but I figured it would be useful to know. Or even know that it's not possible.</p>