被 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'

Supplement 1 · Sep 14, 2021

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

Supplement 2 · Sep 14, 2021

格式化一下

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 replies • 2021-09-16 15:39:09 +08:00

JKeita

Sep 14, 2021

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

AoEiuV020

Sep 14, 2021

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

kingfalse

Sep 14, 2021 via Android

不要为了用而用

Hstar

Sep 14, 2021

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

honkki

Sep 14, 2021

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

Pursue9

Sep 14, 2021

你这样用 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

Sep 14, 2021

不要为了用而用

thetbw

Sep 14, 2021

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

wuwukai007

Sep 14, 2021

顿悟了
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

Sep 14, 2021

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

Rwing

Sep 14, 2021

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

clf

Sep 14, 2021

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

Rwing

Sep 14, 2021

优秀的 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

Sep 14, 2021

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

Trim21

Sep 14, 2021 via Android

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

wuwukai007

Sep 14, 2021

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

Thinklong

Sep 14, 2021

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

spediacn

Sep 14, 2021 via iPhone

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

cp19890714

Sep 14, 2021

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

kevinonepiece

Sep 14, 2021

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

kevinonepiece

Sep 14, 2021

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

cyrivlclth

Sep 14, 2021

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

ragnaroks

Sep 14, 2021

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

512357301

Sep 14, 2021 via Android

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

cp19890714

Sep 14, 2021

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

crystom

Sep 14, 2021

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

Rocketer

Sep 14, 2021 via iPhone

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

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

Mithril

Sep 14, 2021

你需要一个 Linq 。。。

hushao

Sep 14, 2021

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

iseki

Sep 15, 2021

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

gjquoiai

Sep 15, 2021

我超喜欢 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

Sep 15, 2021

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

yalin

Sep 15, 2021

sql 一时爽

thtznet

Sep 15, 2021

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

jakehu

Sep 15, 2021

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

IvanLi127

Sep 15, 2021 via Android

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

pythonee

Sep 15, 2021

@Rwing
@Pursue9

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

seakingii

Sep 16, 2021

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

NCZkevin

Sep 16, 2021

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