Using a subquery in 'FROM' in gorm

Question

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?

Answer 1

Also you can do it with join on a subquery

subQuery := db.
    Select("foo.*").
    Table("foo").
    Where("bar = ?", "baz").
    SubQuery()

db.
    Select("t1.*").
    Join("INNER JOIN ? AS t1 ON t1.id = foo.id", subQuery).
    Where("t1.id = ?", 1)

Answer 2

Author didn't use any "JOIN" in his SQL.

I didn't find any ORM way, but db.Raw works.

subQuery := db.
    Select("foo.*").
    Table("foo").
    Where("bar = ?", "baz").
    SubQuery()

db.Raw("SELECT * FROM ? as t1 WHERE t1.id=?", subQuery, 1).Find(&rows)

Answer 3

FYI – Jinzhu's method doesn't work

I have subqueries working using this method...

var somevalue = 1
row := db.Select("something").Table("first_table").Where("exists(?)", db.Select("a_relationship_to_something").Model(&SecondTable{}).Where("id = ?", somevalue).QueryExpr()).Row()
var result string
row.Scan(&result)

I've tested this with row(), rows(), first(), and find(). You can also use both .Table() and .Model() interchangeably as shown in the example.

Answer 4

also could be used in join

subQuery:=db.Find(&subTable).QueryExpr()

db.
Table("table").
Select("*").
Join("join (?) as t1 on t1.id==table.id", //<---round brackets for multiple rows
subQuery).
Find(&Rows)

Answer 5

while i used "db.Select("").Joins("?", subQuery)" to query, i got "select * from `` (subquery)" whitch is a wrong sql. I should use 'db.Select("").Table(" ").Joins("?", subQuery)'.

subQuery := db.
    Select("foo.*").
    Table("foo").
    Where("bar = ?", "bar")
db := db.
    Select("*").
    Table(" "). // gorm will give you the origin input instead of "``"
    Joins("? as t1", subQuery.QueryExpr()).
    Where("t1.id = ?", 1)

Answer 6

Solved this issue by creating a package for more flexibility: https://github.com/loeffel-io/sql

subquery := sql.Create().
    Select(true, "purchases.*").
    Select(true, "...").
    From(true, "purchases").
    Join(true, "transactions ON transactions.purchase_id=purchases.id")

query := sql.Create().Select(true, "*").
    From(true, "(?) purchases", gorm.Expr(subquery.GetSQL(), subquery.GetValues()...)).
    Join(true, "transactions ON transactions.id=purchases.last_transaction_id")

db.
    Raw(query.GetSQL(), query.GetValues()...).
    Offset(...).
    Limit(...).
    Order(...).
    Unscoped().
    Find(&purchases).
    Error