别名在Knex中的表 [英] Alias a table in Knex

问题描述

我有一个SQL查询，该查询两次引用同一张表，因此我需要将该表别名为两个单独的别名.我不太清楚如何与Knex一起构成.

I have a SQL query that refers to the same table twice, and I need to alias the table to two separate aliases. I can't quite figure out how to compose this with Knex.

有一个单词"表和一个用户"表. Words表具有两个外键，即"author_id"和"winner_id"，引用了Users表的"id"列.

There's a 'Words' table and a 'Users' table. The Words table has two foreign keys, 'author_id' and 'winner_id', referencing the Users table's 'id' column.

这是我要在Knex中编写的SQL:

Here's the SQL I'm trying to compose in Knex:

SELECT w.*, ua.name, uw.name FROM Words AS w
INNER JOIN Users AS ua ON w.author_id = ua.id 
LEFT JOIN Users AS uw ON w.winner_id = uw.id

我对如何在Knex中执行此操作有些迷惑.我的第一次尝试不涉及别名，因此出现了表使用多次"错误.当我尝试使用.as()方法时，knex抱怨缺少.from()子句. .as()方法仅用于别名子查询，我不应该期望它用于别名表吗?

I'm a little lost as to how to do this in Knex. My first attempt didn't involve aliasing, and so I got a 'table used more than once' error. When I tried to use the .as() method, knex complained that there was a missing .from() clause. Is the .as() method only used for aliasing subqueries, and I shouldn't expect it to be used to alias tables?

推荐答案

有两种方法声明标识符(表或列)的别名.一个人可以直接给标识符加上aliasName作为后缀(例如，identifierName作为aliasName)，或者一个人可以传递一个对象{aliasName:'identifierName'}.

There are two ways to declare an alias for identifier (table or column). One can directly give as aliasName suffix for the identifier (e.g. identifierName as aliasName) or one can pass an object { aliasName: 'identifierName' }.

因此，以下代码:

 knex.select('w.*', 'ua.name', 'uw.name')
  .from({ w: 'Words' })
  .innerJoin({ ua: 'Users' }, 'w.author_id', '=', 'ua.id')
  .leftJoin({ uw: 'Users' }, 'w.winner_id', '=', 'uw.id')
  .toString()

将编译为:

select "w".*, "ua"."name", "uw"."name"
from "Words" as "w"
inner join "Users" as "ua" on "w"."author_id" = "ua"."id"
left join "Users" as "uw" on "w"."winner_id" = "uw"."id"

这篇关于别名在Knex中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！