Search code examples
gogo-gorm

Using a subquery in 'FROM' in gorm

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;

I have built the subquery using golang:

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

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

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?

Solution

  • You could use QueryExpr, refer

    https://gorm.io/docs/advanced_query.html#SubQuery

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

    Which generate SQL

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