如何以SELECT * FROM(< subquery>)ORDER BY列的形式构造子查询? [英] How to construct subquery in the form of SELECT * FROM (<subquery>) ORDER BY column;?

问题描述

我正在使用gorm与Postgres数据库进行交互.我正在尝试通过使用DISTINCT ON和此问题的查询来排序记录了这样做并非易事.因此，我需要以

形式进行查询

  SELECT * FROM(< subquery>)ORDER BY列； 

乍一看，看来我需要使用 db.QueryExpr()将我拥有的查询转换为表达式并围绕它构建另一个查询.但是，似乎gorm没有直接指定FROM子句的简单方法.我尝试使用 db.Model(expr)或 db.Table(fmt.Sprint(expr))，但是Model似乎被完全忽略，而 fmt.Sprint(expr)不能完全返回我的想法.表达式包含一些私有变量.如果可以将原始查询转换为完全解析的字符串，则可以使用 db.Table(query)，但不确定是否可以在不运行查询的情况下将其生成为字符串.

如果我有一个完整的gorm查询，如何将它包装在另一个查询中以执行我要执行的ORDER BY?

解决方案

如果要编写将要执行的原始SQL(包括具有SQL子查询的SQL)并将结果使用gorm添加到对象，则可以使用 .Raw()和 .Scan()方法:

  query:=`SELECT子项*FROM(< subquery>)子ORDER BY子列;`db.Raw(query).Scan(& result) 

您将指向对象的指针引用传递给 .Scan()，该对象的结构类似于结果行，与使用 .First()的方式非常相似. .Raw()也可以使用查询中的?将数据添加到查询中，并将这些值作为逗号分隔的输入添加到函数中:

  query:=`SELECT子项*FROM(< subquery>)子在哪里sub.column1 =?AND sub.column2 =?ORDER BY子列;`db.Raw(query，val1，val2).Scan(& result) 

有关如何使用SQL构建器， .Raw()和 .Scan()的更多信息，请查看文档中的示例: http://gorm.io/advanced.html#sql-builder

I am using gorm to interact with a postgres database. I'm trying to ORDER BY a query that uses DISTINCT ON and this question documents how it's not that easy to do that. So I need to end up with a query in the form of

 SELECT * FROM (<subquery>) ORDER BY column;

At first glance it looks like I need to use db.QueryExpr() to turn the query I have into an expression and build another query around it. However it doesn't seem gorm has an easy way to directly specify the FROM clause. I tried using db.Model(expr) or db.Table(fmt.Sprint(expr)) but Model seems to be completely ignored and fmt.Sprint(expr) doesn't return exactly what I thought. Expressions contain a few private variables. If I could turn the original query into a completely parsed string then I could use db.Table(query) but I'm not sure if I can generate the query as a string without running it.

If I have a fully built gorm query, how can I wrap it in another query to do the ORDER BY I'm trying to do?

解决方案

If you want to write raw SQL (including one that has a SQL subquery) that will be executed and the results added to an object using gorm, you can use the .Raw() and .Scan() methods:

query := `
    SELECT sub.*
    FROM (<subquery>) sub
    ORDER BY sub.column;`
db.Raw(query).Scan(&result)

You pass a pointer reference to an object to .Scan() that is structured like the resulting rows, very similarly to how you would use .First(). .Raw() can also have data added to the query using ? in the query and adding the values as comma separated inputs to the function:

query := `
    SELECT sub.*
    FROM (<subquery>) sub
    WHERE
        sub.column1 = ?
        AND sub.column2 = ?
    ORDER BY sub.column;`
db.Raw(query, val1, val2).Scan(&result)

For more information on how to use the SQL builder, .Raw(), and .Scan() take a look at the examples in the documentation: http://gorm.io/advanced.html#sql-builder

这篇关于如何以SELECT * FROM(< subquery>)ORDER BY列的形式构造子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！