在SQLAlchemy中执行“高级”查询遇到困难

Question

我想执行的查询是这个。我把它粘贴在下面：

SELECT p.id, p.title, p.time_submitted, SUM(v.score) as num_votes 
FROM posts p, votes v
WHERE v.postid = p.id
GROUP BY p.id
ORDER BY 
   (SUM(v.score) - 1) / POW(TIMESTAMPDIFF(HOUR,p.time_submitted,NOW()) + INTERVAL 2 HOUR, 1.8) DESC
LIMIT 100

我本来打算用connection.execute手动运行SQL，不用ORM（对象关系映射），但后来发现这样在开发模式下会失败（Pyramid框架），因为sqlite不支持用到的那些函数。

那我该怎么用ORM来执行这个呢？

DBSession().query(Posts).join(Posts.id, Votes.post_id).group_by(Posts.id).order_by(...)

我不知道该怎么继续了>.<

Answer 1

正如你所说，你的查询中有一些函数是sqlite不支持的，比如“pow”。即使你使用SA表达式函数来构建查询，最终的查询还是会包含sqlite不支持的“pow”函数，这样也没用。

当然，sqlite在新项目中使用起来非常简单，但这并不妨碍你在开发时使用其他类型的数据库，在这种情况下你可能确实需要这样做。

如果你还是想尝试一下函数的用法，可以看看你的查询可能会像这样（警告：这段代码未经测试）：

sum_result = func.sum(Vote.score) - 1
time_difference_result = func.pow(func.timestampdiff(text('HOUR'), Post.time_submitted, func.current_timestamp()) + func.interval(2, text('HOUR')), 1.8)
time_difference_result = func.timestampdiff(text('HOUR'), Post.time_submitted, func.now()) + func.interval(2, text('HOUR'))
vote_calculation = (sum_result / time_difference_result).label('vote_calculation')

session.query(Post.id, Post.title, vote_calculation)\
        .join(Vote)\
        .group_by(Post.id, Post.title)\
        .order_by('vote_calculation DESC')[:100]