jOOQ-简洁地在查询中表示列和聚合/窗口函数

问题描述

这篇文章来自我对类似问题的评论：

我正在使用PostgreSQL和jOOQ 3.4，并尝试在jOOQ中表示以下SQL查询：

  SELECT *，COUNT（*）OVER（）
 FROM table1 t1 
 JOIN table2 t2 ON（t1.id = t2.id）
 JOIN table3 t3 ON（t1.otherId = t3.otherId）
  

我喜欢Postgres如何让我简洁地表示所有列加上 count 列，仅用 SELECT *，COUNT（*）OVER（） 。但是，当我尝试在jOOQ中表示相同的查询时，我能做的最简洁的方法是：

  create.select（TABLE1 .fields（））.select（TABLE2.fields（））.select（TABLE3.fields（））.select（count（）。over（））
 .from（TABLE1）
 .join（ TABLE2）.on（TABLE1.ID.equal（TABLE2.ID））
 .join（TABLE3）.on（TABLE1.OTHER_ID.equal（TABLE3.OTHER_ID））
  

理想情况下，我会这样写：

  create.select（）。select（count（）。over（））
 .from（TABLE1）
 .join（TABLE2）.on（TABLE1.ID.equal（TABLE2.ID））
 .join（TABLE3）.on（TABLE1.OTHER_ID.equal（TABLE3.OTHER_ID））
  

但这似乎不起作用。对如何执行此操作有任何想法吗？

解决方案

您已经找到了自己自己的解决方案，这确实是可行的方法使用jOOQ API：

  create.select（TABLE1.fields（））
 .select（TABLE2.fields（））
 .select（TABLE3.fields（））
 .select（count（）。over（））
 ... 
  

从概念上讲，它与此有效的SQL查询相对应：

  SELECT table1。*，table2。*，table3。*，COUNT（*）OVER（）
 ... 
  

操纵jOOQ模型API：

但是，如果这对您来说很烦，您也可以使用以下小技巧来解决此问题：

  //获取对不包含任何SELECT字段的语句中的模型API。
 SelectQuery<？> select = 
 create.select（）
.from（TABLE1）
.join（TABLE2）.on（TABLE1.ID.equal（TABLE2.ID））
.join（ TABLE3）.on（TABLE1.OTHER_ID.equal（TABLE3.OTHER_ID））
 .getQuery（）; 
 
 //复制SELECT语句中的所有字段：
 List< Field<？>>字段=新的ArrayList<>（select.getSelect（））; 
 
 //并明确添加它们：
 select.addSelect（fields）; 
 select.addSelect（count（）。over（））; 
  

这与您最初的尝试一样冗长，但使用起来通常更简单。 / p>

使用派生表

当然，您也可以简单地编写以下等效的SQL查询，是更标准的SQL：

  SELECT t。*，COUNT（*）OVER（）
 FROM（
 SELECT * 
 FROM table1 t1 
 JOIN table2 t2 ON（t1.id = t2.id）
 JOIN table3 t3 ON（t1.otherId = t3.otherId ）
）t 
  

对于jOOQ，这将转换为：

  Table<？> t = select（）
 .from（表1）
 .join（表2）.on（表1.ID.equal（表2.ID））
 .join（表3）.on（表1 .OTHER_ID.equal（TABLE3.OTHER_ID））
 .asTable（ t）; 
 
 create.select（t.fields（），count（）。over（））
 .from（t）; 
  

对星号的支持

jOOQ的未来版本，。不过，目前尚不清楚如何从句法上实现这一目标。

附带说明：

我一直很奇怪PostgreSQL在这里允许这种语法：

  SELECT *，COUNT（*）OVER（）
 ... 
  

几乎不受支持由SQL引擎和有点不可预测。 SQL标准也不允许将独立星号与其他列表达式结合使用。

This post comes as a result of a comment I left on a similar question: https://stackoverflow.com/a/19860271/2308858

I'm using PostgreSQL and jOOQ 3.4 and trying to represent the following SQL query in jOOQ:

SELECT *, COUNT(*) OVER() 
FROM table1 t1
JOIN table2 t2 ON (t1.id = t2.id)
JOIN table3 t3 ON (t1.otherId = t3.otherId)

I like how Postgres lets me concisely represent "all columns plus the count column" with nothing more than SELECT *, COUNT(*) OVER(). But when I try to represent this same query in jOOQ, the most concise way I can do is:

create.select( TABLE1.fields() ).select( TABLE2.fields() ).select( TABLE3.fields() ).select( count().over() )
   .from( TABLE1 )
   .join( TABLE2 ).on( TABLE1.ID.equal( TABLE2.ID ))
   .join( TABLE3 ).on( TABLE1.OTHER_ID.equal( TABLE3.OTHER_ID ))

Ideally, I'd write this instead:

create.select().select( count().over() )
   .from( TABLE1 )
   .join( TABLE2 ).on( TABLE1.ID.equal( TABLE2.ID ))
   .join( TABLE3 ).on( TABLE1.OTHER_ID.equal( TABLE3.OTHER_ID ))

But this doesn't seem to work. Any thoughts on how I can do this?

解决方案

This solution, which you've found yourself, is indeed the way to go with the jOOQ API:

create.select( TABLE1.fields() )
      .select( TABLE2.fields() )
      .select( TABLE3.fields() )
      .select( count().over() )
      ...

It conceptually corresponds to this valid SQL query:

SELECT table1.*, table2.*, table3.*, COUNT(*) OVER()
...

Manipulating the jOOQ "model API":

But if this is annoying to you, you can also work around this issue with this little trick:

// Get access to the "model API" from a statement without any SELECT fields
SelectQuery<?> select =
create.select()
      .from( TABLE1 )
      .join( TABLE2 ).on( TABLE1.ID.equal( TABLE2.ID ))
      .join( TABLE3 ).on( TABLE1.OTHER_ID.equal( TABLE3.OTHER_ID ))
      .getQuery();

// Copy all fields from the SELECT statement:
List<Field<?>> fields = new ArrayList<>(select.getSelect());

// And explicitly add them:
select.addSelect(fields);
select.addSelect(count().over());

This is equally verbose as your original attempt, but might be a bit simpler to use, generically.

Using a derived table

Of course, you could also simply write the following, equivalent SQL query, which would be more standard SQL:

SELECT t.*, COUNT(*) OVER()
FROM (
  SELECT *
  FROM table1 t1
  JOIN table2 t2 ON (t1.id = t2.id)
  JOIN table3 t3 ON (t1.otherId = t3.otherId)
) t

With jOOQ, this would translate to:

Table<?> t = select()
            .from( TABLE1 )
            .join( TABLE2 ).on( TABLE1.ID.equal( TABLE2.ID ))
            .join( TABLE3 ).on( TABLE1.OTHER_ID.equal( TABLE3.OTHER_ID ))
            .asTable("t");

create.select(t.fields(), count().over())
      .from(t);

Support for the asterisk

In a future version of jOOQ, the actual asterisk (*) might be supported explicitly through the jOOQ API. At this point, it is a bit unclear how that can be achieved syntactically, though.

On a side-note:

I have always found it very curious that PostgreSQL allows this syntax here:

SELECT *, COUNT(*) OVER() 
...

It is hardly ever supported by SQL engines and a bit "unpredictable". Neither does the SQL standard allow for a "standalone asterisk" to be combined with other column expressions.

这篇关于jOOQ-简洁地在查询中表示列和聚合/窗口函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！