将SQL中的GROUP_CONCAT与连接操作转换为Peewee代码。

huangapple go评论71阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年6月8日 17:10:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/76430294.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定