关于 sqlalchemy 的计数函数 count()的一个问题

[ziyo12580]

是这样的，我有一个这么一个 query，它的 sql 语句是这样的：

SELECT post.id AS post_id, post.group_id AS post_group_id, post.topic_id AS post_topic_id, post.title AS post_title, post.ukey_origin AS post_ukey_origin, post.ukey_author AS post_ukey_author, post._summary AS post__summary, post.date_created AS post_date_created, post.date_modified AS post_date_modified, post.date_last_replied AS post_date_last_replied, post.date_digested AS post_date_digested, post.date_combined AS post_date_combined, post.is_replyable AS post_is_replyable, post.is_stick AS post_is_stick, post.is_digest AS post_is_digest, post.is_virgin AS post_is_virgin, post.is_anon AS post_is_anon, group_1.id AS group_1_id, group_1.name AS group_1_name, group_1.category_id AS group_1_category_id, group_1.icon AS group_1_icon, group_1.background AS group_1_background, group_1.introduction AS group_1_introduction, group_1.tags AS group_1_tags, group_1.friends_group AS group_1_friends_group, group_1.date_created AS group_1_date_created, group_1.date_modified AS group_1_date_modified, group_1.is_application_required AS group_1_is_application_required, group_1.is_publicly_readable AS group_1_is_publicly_readable, group_1.is_indexable AS group_1_is_indexable, group_1.level AS group_1_level, group_1.custom_info AS group_1_custom_info, group_1.reply_prompt AS group_1_reply_prompt
FROM post LEFT OUTER JOIN post_statistics ON post.id = post_statistics.id JOIN "group" AS group_1 ON group_1.id = post.group_id

但是一旦我执行了 query.count()之后，它的 inner join 就会消失，执行后的 sql 语句是这样子的：

SELECT count(*) AS count_1
FROM (SELECT post.id AS post_id, post.group_id AS post_group_id, post.topic_id AS post_topic_id, post.title AS post_title, post.ukey_origin AS post_ukey_origin, post.ukey_author AS post_ukey_author, post._summary AS post__summary, post.date_created AS post_date_created, post.date_modified AS post_date_modified, post.date_last_replied AS post_date_last_replied, post.date_digested AS post_date_digested, post.date_combined AS post_date_combined, post.is_replyable AS post_is_replyable, post.is_stick AS post_is_stick, post.is_digest AS post_is_digest, post.is_virgin AS post_is_virgin, post.is_anon AS post_is_anon
FROM post LEFT OUTER JOIN post_statistics ON post.id = post_statistics.id) AS anon_1

我看了一下 sqlalchemy orm 的 query 源码，发现里面用到了 from_self()函数，然后我试了一下如果不给这个函数传参数的话，sql 语句里的 inner join 是不会消失的；

但是如果我往里面传了 count(*)这个参数，sql 语句里的 inner join 就不见了；我看了一下 from_self()函数的实现，里面的 entity 参数按理说应该只是覆盖 sql 语句前面的 select 部分，我不明白为什么后面的 from 部分也会被覆盖掉。

希望有大神能看看，谢谢了

[RW667]

代码都没有， 怎么看看。。

[CRVV]

大概看了一下
如果 group 的 id 字段有 UNIQUE 约束，post 的 id 字段有 NOT NULL 和 REFERENCES group(id) 约束
那么 INNER JOIN 和 count 的结果无关，可以被优化掉

我从来没用过 SQLAlchemy，至于是怎么优化掉的就不清楚了