title: Flask-sqlalchemy efficiently get count of query ordered by relationship
body: <p>I have 2 models: <code>Post</code> and <code>PostLike</code>. I want to return a query of Posts filtered by their PostLike <code>created_at</code> field, as well as return the total count of posts in that query efficiently (without using <code>.count()</code>).</p>
  <p>Simplified models:</p>
  <pre><code>class Post(db.Model):
      ...
      post_likes = db.relationship('PostLike', back_populates=&quot;post&quot;)
  
  class PostLike(db.Model):
      ...
      created_at = db.column(db.Float)
  
      post_id = db.Column(UUID(as_uuid=True), db.ForeignKey(&quot;post.id&quot;), index=True)
      post = db.relationship(&quot;Post&quot;, back_populates=&quot;post_likes&quot;, foreign_keys=[post_id])
  </code></pre>
  <p>Here are the queries I'm trying to run:</p>
  <pre><code># Get all posts
  posts = Post.query.join(PostLike, Post.post_likes).order_by(PostLike.created_at.desc()).all()
  
  # Get total # of posts
  posts = Post.query.join(PostLike, Post.post_likes).order_by(PostLike.created_at.desc()).count()
  </code></pre>
  <p>There are 3 problems with those queries.</p>
  <ol>
  <li><p>I'm not sure those queries are the best for my use case. Are they?</p>
  </li>
  <li><p>The query returns the wrong number as count. The count query returns a number higher than the results of the <code>.all()</code> query. Why?</p>
  </li>
  <li><p>This is not performant as it is calling directly <code>.count()</code>. How do I implement an efficient query to also retrieve the count? Something like <code>.statement.with_only_columns([func.count()])</code>?</p>
  </li>
  </ol>
  <p>I'm using Postgres, and I'm expecting up to millions of rows to count. How do I achieve this efficiently?</p>