WHERE语句在JOIN上具有重复的列名-PostgreSQL [英] WHERE statement with duplicate column names over JOIN - PostgreSQL

问题描述

我正在尝试联接两个表，一个 plans 表和一个 plan_details 表.以下是表格外观的两个示例.

I am trying to join two tables, a plans table and a plan_details table. Below are two examples of what the tables look like.

计划表

+---------+------+-----------+
| user_id | plan | is_active |
+---------+------+-----------+
|  1      |  10  |    true   |
|  1      |  11  |   false   |
|  2      |  11  |    true   |

PLAN_DETAILS表

+---------+------+-------+-----------+
| plan_id | cost | price | is_active |
+---------+------+-------+-----------+
|  10     |  19  |  199  |    true   |
|  11     |  13  |  149  |    true   |

我只想提取与每个用户相关的有效计划 cost 和 price .现在，我的knex语句是:

I only want to only pull the active plan cost and the price related to each user. Right now, my knex statement is:

knex('plans')
  .where({
    user_id: 1,
    is_active: 'true'
  })
  .select(
    'plans.plan',
    'plan_details.cost',
    'plan_details.price'
  )
  .join('plan_details as plan_details', 'plan_details.plan_id', 'plans.plan')
  .then(function (user_plan_id) {
    console.log(user_plan_id);
  });

现在，如果我将is_active: 'true'保留在其中，那么我会得到一个Unhandled rejection error: column reference "is_active" is ambiguous.如果我删除了is_active部分，那么即使我只想要有关该用户哪些计划处于活动状态的信息，我也会获得两个引用该用户的计划的信息.

Now if I keep the is_active: 'true' in there then I get a Unhandled rejection error: column reference "is_active" is ambiguous. If I take out the is_active part, well then I get information for both of the plans that reference the user even though I only want the info regarding which plans are active for the user.

如何仅获取用户的活动计划?我使用KNEX.JS作为我的ORM，但我也很高兴为此使用原始SQL.

How do I get only the active plans for a user? I am using KNEX.JS as my ORM, but I am happy to use raw SQL as well for this.

推荐答案

使用knex可以做到:

With knex this should do:

knex('plans')
  .select(
    'plans.plan',
    'plan_details.cost',
    'plan_details.price'
  )
  .join('plan_details as plan_details', 'plan_details.plan_id', 'plans.plan')
  .where('plan.user_id', 1)
  .where('plan.is_active', true)
  .then(function (user_plan_id) {
    console.log(user_plan_id);
  });

这篇关于WHERE语句在JOIN上具有重复的列名-PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！