sqlalchemy: paginate does not return the expected number of elements

Question

I am using flask-sqlalchemy together with a sqlite database. I try to get all votes below date1

sub_query = models.VoteList.query.filter(models.VoteList.vote_datetime < date1)
sub_query = sub_query.filter(models.VoteList.group_id == selected_group.id)
sub_query = sub_query.filter(models.VoteList.user_id == g.user.id)
sub_query = sub_query.subquery()
old_votes = models.Papers.query.join(sub_query, sub_query.c.arxiv_id == models.Papers.arxiv_id).paginate(1, 4, False)

where the database model for VoteList looks like this

class VoteList(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
group_id = db.Column(db.Integer, db.ForeignKey('groups.id'))
arxiv_id = db.Column(db.String(1000), db.ForeignKey('papers.arxiv_id'))
vote_datetime = db.Column(db.DateTime)
group = db.relationship("Groups", backref=db.backref('vote_list', lazy='dynamic'))
user = db.relationship("User", backref=db.backref('votes', lazy='dynamic'), foreign_keys=[user_id])
def __repr__(self):
 return '<VoteList %r>' % (self.id)

I made sure that the 'old_votes' selection above has 20 elements. If I use .all() instead of .paginate() I get the expected 20 result? Since I used a max results value of 4 in the example above I would expect that old_votes.items has 4 elements. But it has only 2? If I increase the max results value the number of elements also increases, but it is always below the max result value? Paginate seems to mess up something here? any ideas? thanks carl

EDIT I noticed that it works fine if I apply the paginate() function on add_columns(). So if I add (for no good reason) a column with

old_votes = models.Papers.query.join(sub_query, sub_query.c.arxiv_id == models.Papers.arxiv_id)
old_votes = old_votes.add_columns(sub_query.c.vote_datetime).paginate(page, VOTES_PER_PAGE, False)

it works fine? But since I don't need that column it would still be interesting to know what goes wrong with my example above?

Answer 1

Looks to me that for the 4 rows returned (and filtered) by the query, there are 4 rows representing 4 different rows of the VoteList table, but they refer/link/belong to only 2 different Papers models. When model instances are created, duplicates are filtered out, and therefore you get less rows. When you add a column from a subquery, the results are tuples of (Papers, vote_datetime), and in this case no duplicates are removed.

• carl: is there a way to prevent the removal of duplicates?
• van: I am not sure what would be the purpose, but I think that if you query for a tuple instead of a model instances, it will do the trick. So instead of models.Paper.query... (which is (almost) an alias to session.query(Paper)) do session.query([Paper]).... But the results would be not instances of Paper, but tuples with instances of Paper.

Answer 2

I encountered the same issue and I applied van's answer but it did not work. However I agree with van's explanation so I added .distinct() to the query like this:

old_votes = models.Papers.query.distinct().join(sub_query, sub_query.c.arxiv_id == models.Papers.arxiv_id).paginate(1, 4, False)

It worked as I expected.

• jack west: Adding .distinct() also worked for me on a complex query with multiple subqueries