在gorm的'FROM'中使用子查询 [英] Using a subquery in 'FROM' in gorm

问题描述

我想知道如何使用gorm在FROM子句中使用子查询.看起来像以下内容:

I would like to know how I can use a subquery in FROM clause using gorm. It would look like the following:

SELECT * FROM 
(
  SELECT foo.*
  FROM foo
  WHERE bar = "baz"
) AS t1
WHERE t1.id = 1;

我已经使用golang构建了子查询:

I have built the subquery using golang:

db.Model(Foo{}).Where("bar = ?", "baz")

但是如何在 FROM 中将其用作子查询?

But how can I use this as a subquery in FROM?

如果有一种方法可以将gorm查询转换为SQL字符串，那么我可以简单地将该字符串插入原始SQL.但是似乎没有这种方法.有什么建议吗?

If there is a method that turns a gorm query into a SQL string, then I can simply plug that string into a raw SQL. But there does not seem to be such method. Any suggestions?

推荐答案

您可以使用 QueryExpr ，请参见

http://jinzhu.me/gorm/crud.html#subquery

db.Where("amount>?"，DB.Table("orders").Select("AVG(amount)").Where("state =?"，"paid").QueryExpr()).Find(& orders)

db.Where("amount > ?", DB.Table("orders").Select("AVG(amount)").Where("state = ?", "paid").QueryExpr()).Find(&orders)

生成SQL的

SELECT *从"orders"到"orders"."deleted_at"为NULL并且(金额>(从订单"的位置(状态=已付款"中选择平均AVG(金额))))；

SELECT * FROM "orders" WHERE "orders"."deleted_at" IS NULL AND (amount > (SELECT AVG(amount) FROM "orders" WHERE (state = 'paid')));

这篇关于在gorm的'FROM'中使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！