Join方法中SELECT附近的Knex查询语法错误 [英] Knex query syntax error near SELECT in the join method

问题描述

我正在尝试使用2个表的Knex建立连接.我所做的查询给了我语法错误

语法错误，位于\"select \"

或附近

我建立的查询

  this.builder.加入(this.tx(messageTable).select(messageColumns.conversationId).max(messageColumns.createdAt，'updated_at').where(messageColumns.conversationId，columns.id).groupBy(messageColumns.conversationId)，messageColumns.conversationId，'updated_at') 

生成的SQL在连接后的SELECT中也给出了相同的语法错误

  SELECT*从对话"指的是对话".内部联接选择"conversation_id"，max("created_at")从消息"在哪里"conversation_id"='id'通过...分组"conversation_id"开启"conversation_id"="updated_at"； 

在knex中避免语法错误的正确方法是什么

解决方案

我决定回答自己的问题，因为将来有人可能也会遇到类似的问题.

该错误是由以下事实引起的:KnexJS是在 INNER JOIN 上没有括号(...)的情况下生成SQL的，因此正确的SQL应该如下所示

INNER JOIN(SELECT ...)

然后使用正确的Knex，如下所示

  this.builder.加入(函数innerSelect(){this.select(messageColumns.conversationId).from(消息表).max(`$ {messageTable}.$ {messageColumns.createdAt} as Updated_at`).groupBy(messageColumns.conversationId).as('m');}，messageColumns.conversationId，'ID') 

原因是让您以需要添加 function 的正确方式来了解Knex子查询，文档 解决方案

I decided to answer my own question as maybe someone could have similar issue in the future.

The error was given by the fact KnexJS was generated an SQL on the INNER JOIN without parenthesis (...) so the right SQL should look as follow

INNER JOIN ( SELECT ... )

The right Knex then was as follow to use

this.builder
      .join(
        function innerSelect() {
          this.select(messageColumns.conversationId)
            .from(messageTable)
            .max(`${messageTable}.${messageColumns.createdAt} as updated_at`)
            .groupBy(messageColumns.conversationId)
            .as('m');
        },
        messageColumns.conversationId,
        'id'
      )

The reason is to let understand the Knex a subquery in the correct way you need to add a function and similar is showed in the docs http://knexjs.org/#Builder-join

这篇关于Join方法中SELECT附近的Knex查询语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！