Using a subquery in 'FROM' in gorm

go go-gorm
Sung Cho picture Sung Cho · Oct 18, 2017 · Viewed 9.9k times · Source

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

Mykyta Nikitenko picture Mykyta Nikitenko · Aug 3, 2018

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)

Related questions

Gorm Golang orm associations

I'm using Go with the GORM ORM. I have the following structs. The relation is simple. One Town has multiple Places and one Place belongs to one Town. type Place struct { ID int Name string Town Town } type Town struct { …

Read More
How to Create or Update a record with GORM?

Gorm has a FirstOrCreate method and a FirstOrInit but how to check afterwards if the record was actually created? I like to create a record if it does not exists and if it exists I want to update some fields.

Read More
reflect.Value.Set using unaddressable value

g.GET("/", func(c echo.Context) error { var users []models.User err := db.Find(users).Error if err != nil { fmt.Println(err) } return c.JSON(http.StatusOK, users) }) this is the code for getting and displaying users from table using …

Read More