Home Optional query across relation
Reply: 0

Optional query across relation

user6644
1#
user6644 Published in September 20, 2018, 8:44 pm

I have two tables to handle coupon codes in my application.

The first (coupon) stores coupon codes, along with the discount they provide:

| id | code       | public | discount_percent |
|----|------------|--------|------------------|
| 1  | EXAMPLE    | true   | 10               |
| 2  | PRIV_12981 | false  | 30               |

Public indicates whether the coupon code can be claimed by anyone.

The second (user_coupon) table stores users entitled to redeem private coupons, and whether their instance of the coupon has been redeemed:

| id | coupon_id | user_id | redeemed |
|----|-----------|---------|----------|
| 1  | 2         | 1       | false    |

I need to make the following query (pseudo code):

SELECT * from coupons
    WHERE (
        code = 'PRIV_12981'
        AND (
            public = true
            OR (
                user_coupon.user_id = 1
                AND user_coupon.redeemed = false
            )
        )
   )

This query should check whether a coupon exists for the provided code that is either public, or has a corresponding entry in the user_coupon table for the provided user_id and has not already been redeemed.

I'm using Sequelize, and have written the following query:

Coupon.findOne({
  where: {
    [Sequelize.Op.and]: [
      {
        code: 'TEST'
      },
      {
        [Sequelize.Op.or]: [
          {
            public: true
          },
          {
            [Sequelize.Op.and]: [
              Sequelize.where(
                Sequelize.col('user_coupons.redeemed'), false
              ),
              Sequelize.where(
                Sequelize.col('user_coupons.user_id'), 1
              )
            ]
          }
        ]
      }
    ]
  },
  include: [{
    model: UserCoupon,
    as: 'user_coupons',
  }]
}).then(coupon => {
  console.log(coupon)
})

This generates the following SQL:

SELECT "Coupon".*,
       "user_coupons"."id" AS "user_coupons.id",
       "user_coupons"."coupon_id" AS "user_coupons.coupon_id",
       "user_coupons"."user_id" AS "user_coupons.user_id",
       "user_coupons"."redeemed" AS "user_coupons.redeemed",
       "user_coupons"."created_at" AS "user_coupons.created_at",
       "user_coupons"."updated_at" AS "user_coupons.updated_at"
FROM
  (SELECT "Coupon"."id",
          "Coupon"."code",
          "Coupon"."public",
          "Coupon"."discount_percent",
          "Coupon"."max_discount",
          "Coupon"."valid_from",
          "Coupon"."invalid_after",
          "Coupon"."created_at",
          "Coupon"."updated_at"
   FROM "public"."coupon" AS "Coupon"
   WHERE ("Coupon"."code" = 'TEST'
          AND ("Coupon"."public" = TRUE
               OR ("user_coupons"."redeemed" = FALSE
                   AND "user_coupons"."user_id" = 1)))
   LIMIT 1) AS "Coupon"
LEFT OUTER JOIN "public"."user_coupon" AS "user_coupons" ON "Coupon"."id" = "user_coupons"."coupon_id";

However, it throws the following error:

missing FROM-clause entry for table "user_coupons"

What have I done wrong with the Sequelize query?

share|improve this question

1 Answer 1

active oldest votes
up vote 0 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.303202 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO