英文:
Converting SQL GROUP_CONCAT with concatenation to Peewee code
问题
I'm trying to convert an SQL query that uses the GROUP_CONCAT function with concatenation to Peewee code. However, I'm having trouble with the concatenation part.
Here's the original SQL code that I want to convert:
SELECT GROUP_CONCAT(' ' || t2.value1 || '|' || t2.value2) AS rates
It works with sqLite terminal but not sure how to convert it to peewee.
So far, I tried to use + operator
and fn.concat()
like below, but it didn't work as expected.
fn.GROUP_CONCAT(
fn.concat(' ', ModelName.t2.value1, '|', ModelName.t2.value2)
).alias('rates')
When I use fn.concat()
, my query.objects()
return error for len()
like below:
TypeError: object of type 'NoneType' has no len()
英文:
I'm trying to convert an SQL query that uses the GROUP_CONCAT function with concatenation to Peewee code. However, I'm having trouble with the concatenation part.
Here's the original SQL code that I want to convert:
SELECT GROUP_CONCAT(' ' || t2.value1 || '|' || t2.value2) AS rates
It works with sqLite terminal but not sure how to convert it to peewee.
So far, I tried to use + operator
and fn.concat()
like below, but it didnt worked as expected.
fn.GROUP_CONCAT(' ' + ModelName.t2.quantity + '|' + ModelName.t2.rate).alias('rates')
fn.GROUP_CONCAT(
fn.concat(' ', ModelName.t2.value1, '|', ModelName.t2.value2)
).alias('rates')
When I use fn.concat(), my query.objects()
return error for len()
like below
TypeError: object of type 'NoneType' has no len()
答案1
得分: 0
Peewee覆盖了"+"用于字符串类型的列以执行连接操作,但我猜想你的字段类型必须是数字。在这种情况下,你可以这样做:
class Amt(db.Model):
key = TextField()
val1 = IntegerField()
val2 = IntegerField()
db.create_tables([Amt])
for i in range(10):
for j in range(1, i + 3):
Amt.create(key='k%s' % i, val1=j, val2=j * 2)
exp = Value(' ').concat(Amt.val1).concat('|').concat(Amt.val2)
q = (Amt.select(fn.GROUP_CONCAT(exp).alias('rates')).group_by(Amt.key))
for row in q:
print(row.rates)
输出:
1|2, 2|4
1|2, 2|4, 3|6
1|2, 2|4, 3|6, 4|8
1|2, 2|4, 3|6, 4|8, 5|10
1|2, 2|4, 3|6, 4|8, 5|10, 6|12
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18, 10|20
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18, 10|20, 11|22
英文:
Peewee overrides "+" for string-type columns to perform concat, but I'm guessing your field-types must be numeric. In that case, you can do the following:
class Amt(db.Model):
key = TextField()
val1 = IntegerField()
val2 = IntegerField()
db.create_tables([Amt])
for i in range(10):
for j in range(1, i + 3):
Amt.create(key='k%s' % i, val1=j, val2=j * 2)
exp = Value(' ').concat(Amt.val1).concat('|').concat(Amt.val2)
q = (Amt.select(fn.GROUP_CONCAT(exp).alias('rates')).group_by(Amt.key))
for row in q:
print(row.rates)
Outputs:
1|2, 2|4
1|2, 2|4, 3|6
1|2, 2|4, 3|6, 4|8
1|2, 2|4, 3|6, 4|8, 5|10
1|2, 2|4, 3|6, 4|8, 5|10, 6|12
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18, 10|20
1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18, 10|20, 11|22
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论