WHERE statement with duplicate column names over J

Question

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.

PLANS Table

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

PLAN_DETAILS Table

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

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);
  });

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.

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.

Answer 1

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);
  });

Answer 2

SQL would be similar to:

select 
  plans.plan,
  plan_details.cost,
  plan_details.price
from plan
join plan_details on plans.plan = plan_details.plan_id
where plan.is_active