被 ORM 折磨， sql 1 分钟写好， ORM 想了一个小时，没写出来😭

select d.id,d.created_time,(select count(*) from word where word.dictionary_id=d.id)
    as word_count,
       (select account from user where user.id = d.creator_id) as username,
       d.dictionary_name
        from dictionary
    as d where d.thesaurus_name='professional'

第 1 条附言 · 2021-09-14 16:24:23 +08:00

word_count = select(func.count(Word.id)).where(Word.dictionary_id==Dictionary.id).label('word_count') username = select(User.account).where(User.id == Dictionary.creator_id).as_scalar().label('username') s = select(Dictionary.created_time,word_count,username).where(Dictionary.thesaurus_name=='professional')

SELECT dictionary.created_time, (SELECT count(word.id) AS count_1 
FROM word 
WHERE word.dictionary_id = dictionary.id) AS word_count, (SELECT "user".account 
FROM "user" 
WHERE "user".id = dictionary.creator_id) AS username 
FROM dictionary 
WHERE dictionary.thesaurus_name = :thesaurus_name_1

第 2 条附言 · 2021-09-14 18:43:35 +08:00

格式化一下

from sqlalchemy import select ,func
word_count = select(func.count(Word.id)).where(Word.dictionary_id==Dictionary.id).label('word_count')
username = select(User.account).where(User.id == Dictionary.creator_id).as_scalar().label('username')
sql = select(Dictionary.created_time,word_count,username).where(Dictionary.thesaurus_name=='professional')
print(s)
'''
SELECT dictionary.created_time, (SELECT count(word.id) AS count_1 
FROM word 
WHERE word.dictionary_id = dictionary.id) AS word_count, (SELECT "user".account 
FROM "user" 
WHERE "user".id = dictionary.creator_id) AS username 
FROM dictionary 
WHERE dictionary.thesaurus_name = :thesaurus_name_1
'''

39 条回复 • 2021-09-16 15:39:09 +08:00

JKeita

2021-09-14 15:39:56 +08:00

那就别用呗，orm 性能又不是说有多好。又不直观。维护又麻烦。

AoEiuV020

2021-09-14 15:40:18 +08:00

会 sql 的话 orm 也都能直接执行 sql 的吧，或者能随便写写转成 sql 对比一下再调整，

kingfalse

2021-09-14 15:44:21 +08:00 via Android

不要为了用而用

Hstar

2021-09-14 15:45:03 +08:00

里面那个 select count(*) from word where word.dictionary_id=d.id 子查询不好写，建议先查了变成一个字典再拼接

honkki

2021-09-14 15:45:24 +08:00

orm 也可以直接执行 sql 原生语句的呀

Pursue9

2021-09-14 15:49:55 +08:00

你这样用 SQL，真的不怕数据库蹦吗

建议先查` dictionary `
```sql
SELECT
d.id,
d.created_time
FROM dictionary AS d
WHERE d.thesaurus_name='professional'
LIMIT 1000
```
再去查 word_count user
```sql
SELECT id,COUNT(1)
FROM word_count
WHERE id IN ()
```

```sql
SELECT account FROM `user`
WHERE id IN ()
```

然后返回的结果再用程序处理后返回

qW7bo2FbzbC0

2021-09-14 15:52:23 +08:00

不要为了用而用

thetbw

2021-09-14 15:52:33 +08:00

如果数据量不是很大的话分成三个查询如何，
第一个查询查 select d.id,d.created_time d.dictionary_name from dictionary
as d where d.thesaurus_name='professional'
后两个查询分别为上面的子查询，然后组装数据

wuwukai007

2021-09-14 16:23:26 +08:00

顿悟了
word_count = select(func.count(Word.id)).where(Word.dictionary_id==Dictionary.id).label('word_count')
username = select(User.account).where(User.id == Dictionary.creator_id).as_scalar().label('username')
s = select(Dictionary.created_time,word_count,username).where(Dictionary.thesaurus_name=='professional')

Latin

2021-09-14 18:01:37 +08:00

@wuwukai007 这不还是一次查询分了三次吗（狗头

Rwing

2021-09-14 18:07:51 +08:00

不是我说。。。这也叫 ORM 吗。。。。不就是把 sql 关键字替换成了函数。。。。。。

clf

2021-09-14 18:10:56 +08:00

换个 ORM 吧，理想的 ORM 应该是封装了单表查询的方法，多表通过注解关联两个类的字段或者是配置文件配置，进一步的甚至可以用 lambda 方法来构造跨表查询。

Rwing

2021-09-14 18:23:52 +08:00

优秀的 orm 应该是这样的

from dict in db.dictionary
where dict.Thesaurus_name == 'professional'
select new
{
dict = dict,
wordCount = dict.Word.Count(),
userName = dict.User.Name
};

或者 S
db.dictionary
.Where(d => d.Thesaurus_name == 'professional')
.Select(d =>{
new {
dict = d,
wordCount = d.Word.Count(),
userName = d.User.Name
}
})

Pursue9

2021-09-14 18:41:47 +08:00

@Rwing EFCore 啊，应该除了 C# /F#，别的语言应该实现不了这种 linq 这种伪 SQL

Trim21

2021-09-14 18:48:08 +08:00 via Android

你可以直接执行 SQL 然后把结果序列化成 ORM 的类啊…

wuwukai007

2021-09-14 18:50:14 +08:00

@Trim21 @AoEiuV020 @JKeita 直接用 sql 在参数不固定时，会有恶心的最后一个 and 的问题，要处理 where 1= 1 这种

Thinklong

2021-09-14 19:17:49 +08:00

所有连表查询都应该被干掉，再好的 ORM 也不是你作案的工具

spediacn

2021-09-14 20:00:50 +08:00 via iPhone

牛叉组件都是绕开 ORM 的，比如 ms 的 identity，直接底层调用存储过程

cp19890714

2021-09-14 20:14:37 +08:00

1. 简单查询用 ORM, 稍复杂的都直接写 sql
2. 把复杂查询拆成多个简单查询.

kevinonepiece

2021-09-14 20:20:01 +08:00

@Pursue9 连表查只用一次 MySQL 连接，分开查得三次连接，我之前把三次简单查询合并成一次，速度快了，所以该怎么权衡呢？

kevinonepiece

2021-09-14 20:21:01 +08:00

@cp19890714
@Thinklong 为啥要干掉连表查

cyrivlclth

2021-09-14 20:26:16 +08:00

@kevinonepiece 这次快了，请求多了，数据库遭不住。。。好多子查询。。。

ragnaroks

2021-09-14 21:41:01 +08:00

@Pursue9
linq 好像 csharp 独一家，不过转成方法的话基本上所有语言都有。
比如这样的语法 ↓
var ageList=table().select(e=>e.age).where(e=>e.age>18).list()

512357301

2021-09-14 22:20:53 +08:00 via Android

原始 SQL 的写法本质上还是 left join 吧，你的那个写法应该是 mysql 独有或者不算特别标准的写法，
你可以把那两个子查询从 select 那里挪到 from 后面，用 left join 关联，这样会不会更好用 orm 实现呢。

cp19890714

2021-09-14 22:25:59 +08:00

@kevinonepiece
以我个人的使用经验, 我觉得多次简单查询对比关联查询有以下好处:
* 有效使用数据库缓存
* 关联的表多了,且没有用好索引, 一次查询的时间就更长. 这种并发查询多了, 就会导致数据库压力骤增. 例如:一次查询要 100ms,那么在未来很可能成为慢查询,进而可能导致雪崩.
* 减少锁的竞争
* 尽量降低数据库压力, 毕竟数据库的扩容比服务器扩容难多了. 在开发时,就让 sql 足够简单, 未来一旦出现数据库瓶颈, 大部分的代码不用考虑 sql 优化了, 直接升级数据库吧.
* 随着数据量的增加, mysql 的执行逻辑也会变化. 虽然开发时不是慢查询,但以后可能就会变成慢查询.
但并不是所有的关联查询都拆分, 对于效率非常高的关联查询, 还是不要拆分.

crystom

2021-09-14 22:31:33 +08:00

做 olap 分析能连表就连表，跟 oltp 场景不同

Rocketer

2021-09-14 22:33:44 +08:00 via iPhone

不用死脑筋，.Net 程序员也不是非 linq 不可，复杂查询还是会用 sql 的。

问题是你这 sql 不敢直接在生产环境用，怕是要被 dba 打死的。

Mithril

2021-09-14 22:47:08 +08:00

你需要一个 Linq 。。。

hushao

2021-09-14 23:44:29 +08:00

你都用 sqlalchemy，并且 sql 都写好了，实在写不出 orm 直接执行 sql 语句就行啊，要求必须使用 orm 的另说。

iseki

2021-09-15 00:21:29 +08:00

一般 ORM 都支持 raw sql 的吧，ORM 不适合不 O 的场景，统计之类的就非常不 O，强用 ORM 就是给自己找不痛快

gjquoiai

2021-09-15 01:01:59 +08:00

我超喜欢 sqlalchemy 的，我来给你写：
d = dictionary.alias()
w = word.alias()
u = user.alias()
select(
d.c.id,
d.c.created_time,
select(func.count("*").label("word_count")).select_from(w).where(w.c.dictionary_id == d.c.id).subquery(),
select(u.c.account).where(u.c.id == d.c.creator_id).subquery(),
).select_from(d).where(d.c.thesaurus_name == "professional")

gjquoiai

2021-09-15 01:07:01 +08:00

@gjquoiai #31 似乎应该把 subquery 换成 scalar_subquery，如果找不到关联表的话再加一个 correlate （

yalin

2021-09-15 03:46:07 +08:00

sql 一时爽

thtznet

2021-09-15 08:58:35 +08:00

查询和命令分离，只是查询可以不用 ORM 直接运行 SQL 效率还高，命令涉及到领域持久化，用 ORM 比较适合。

jakehu

2021-09-15 10:58:54 +08:00

用这个呗，即集成了 SQLAlchemy Core，又可以用原生写法。一个字，好用
https://github.com/encode/databases

IvanLi127

2021-09-15 12:45:51 +08:00 via Android

从 orm 中提取表名字段名，然后拼接 sql 的时候用上这些值，重构还有希望

pythonee

2021-09-15 15:07:27 +08:00

@Rwing
@Pursue9

我就说，这种写法看着怎么这么爽

seakingii

2021-09-16 10:48:03 +08:00

复杂的查询可以建存储过程,再调用

NCZkevin

2021-09-16 15:39:09 +08:00

@jakehu 想咨询一下，databases 插入的时候值一定要是字典吗，网上搜 databases 这个词太容易搜到别的东西了，很难搜到相关的。官方例子是
```
values = [
{"text": "example2", "completed": False},
{"text": "example3", "completed": True},
]
```
能不能支持这种？
```
[
["example2",False] ,["example2",True]
]
```