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

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

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.

  1. fn.GROUP_CONCAT(
  2. fn.concat(' ', ModelName.t2.value1, '|', ModelName.t2.value2)
  3. ).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')

  1. fn.GROUP_CONCAT(
  2. fn.concat(' ', ModelName.t2.value1, '|', ModelName.t2.value2)
  3. ).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覆盖了"+"用于字符串类型的列以执行连接操作,但我猜想你的字段类型必须是数字。在这种情况下,你可以这样做:

  1. class Amt(db.Model):
  2. key = TextField()
  3. val1 = IntegerField()
  4. val2 = IntegerField()
  5. db.create_tables([Amt])
  6. for i in range(10):
  7. for j in range(1, i + 3):
  8. Amt.create(key='k%s' % i, val1=j, val2=j * 2)
  9. exp = Value(' ').concat(Amt.val1).concat('|').concat(Amt.val2)
  10. q = (Amt.select(fn.GROUP_CONCAT(exp).alias('rates')).group_by(Amt.key))
  11. for row in q:
  12. print(row.rates)

输出:

  1. 1|2, 2|4
  2. 1|2, 2|4, 3|6
  3. 1|2, 2|4, 3|6, 4|8
  4. 1|2, 2|4, 3|6, 4|8, 5|10
  5. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12
  6. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14
  7. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16
  8. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18
  9. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18, 10|20
  10. 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:

  1. class Amt(db.Model):
  2. key = TextField()
  3. val1 = IntegerField()
  4. val2 = IntegerField()
  5. db.create_tables([Amt])
  6. for i in range(10):
  7. for j in range(1, i + 3):
  8. Amt.create(key='k%s' % i, val1=j, val2=j * 2)
  9. exp = Value(' ').concat(Amt.val1).concat('|').concat(Amt.val2)
  10. q = (Amt.select(fn.GROUP_CONCAT(exp).alias('rates')).group_by(Amt.key))
  11. for row in q:
  12. print(row.rates)

Outputs:

  1. 1|2, 2|4
  2. 1|2, 2|4, 3|6
  3. 1|2, 2|4, 3|6, 4|8
  4. 1|2, 2|4, 3|6, 4|8, 5|10
  5. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12
  6. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14
  7. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16
  8. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18
  9. 1|2, 2|4, 3|6, 4|8, 5|10, 6|12, 7|14, 8|16, 9|18, 10|20
  10. 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:

确定