In Objection.js, how can I include a virtual column with my query?

user_7387269 0
Viewed 850

I would like to create an expired_bool virtual column.

I have the following 3 tables:

subscriptions:

id duration price
1 30 0.99
2 360 1.99

invoices:

id user_id purchase_date
1 34 2020-01-01 01:21:01
2 42 2021-02-19 19:19:19

invoice_items:

id user_id invoice_id subscription_id activation_date
1 34 1 1 2020-05-15 12:51:51
2 34 1 2 2021-02-20 20:20:12
3 42 2 1 NULL
4 42 2 2 2021-02-20 20:20:12

This is how the User, Invoice, and InvoiceItems tables are modeled in Objection.js:

class User extends Model {
  static get tableName() {
    return "users";
  }

  static get relationMappings() {
    return {
 
      invoices: {
        relation: Model.HasManyRelation,
        modelClass: Invoice,
        join: {
          from: "users.id",
          to: "invoices.user_id",
        },
      },

      invoiceItems: {
        relation: Model.HasManyRelation,
        modelClass: InvoiceItem,
        join: {
          from: "users.id",
          to: "invoice_items.user_id",
        },
      },

    };
  }
}

class Invoice extends Model {
  static get tableName() {
    return "invoices";
  }

  static get relationMappings() {
    return {
      user: {
        relation: Model.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: "invoices.user_id",
          to: "users.id",
        },
      },

      invoiceItems: {
        relation: Model.HasManyRelation,
        modelClass: InvoiceItem,
        join: {
          from: "invoices.id",
          to: "invoice_items.invoice_id",
        },
      },
    };
  }
}

class InvoiceItem extends Model {
  static get tableName() {
    return "invoice_items";
  }

  static get relationMappings() {
    return {
      invoice: {
        relation: Model.BelongsToOneRelation,
        modelClass: Invoice,
        join: {
          from: "invoice_items.invoice_id",
          to: "invoice.id",
        },
      },

      user: {
        relation: Model.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: "invoice_items.user_id",
          to: "users.id",
        },
      },

      subscription: {
        relation: Model.HasOneRelation,
        modelClass: Subscription,
        join: {
          from: "invoice_items.subscription_id",
          to: "subscriptions.id",
        },
      },
    };
  }
}

And this is how I'm Querying for the user and his/her purchases:

async function getUserAllWithPasswordByIdAsync(userId) {
  try {
    const query = await User.query()
      .findById(userId)
      .withGraphFetched("invoiceItems.subscription")
      .withGraphFetched("invoices")

    return query;
  }
  catch (error) {
    console.log(error)
  }
}

Just as an aside, the expired_bool virtual column is determined by checking the activation date of invoiceItems and adding the duration from the subscriptions table, and making sure that date is in the future from today's date.

So in summary, how can I determine if the invoiceItem is expired automatically (by use of a virtual column, which should be added to the invoiceItems table), and ensure that this is included with my query of User?

objection.js knex.js javascript
2 Answers

One possible solution would be to add expiry_date column to invoice_items.

Both activation_date and expiry_date will be set during subscription activation

activation_date = today's date
expiry_date = activation_date + subscription duration

invoice_items:

id user_id invoice_id subscription_id activation_date expiry_date
1 34 1 1 2020-05-15 12:51:51 2020-06-14 12:51:51
2 34 1 2 2021-02-20 20:20:12 2022-02-15 20:20:12
3 42 2 1 NULL NULL
4 42 2 2 2021-02-20 20:20:12 2022-02-15 20:20:12

Query for active invoice_items will be as simple as

select "invoice_items".* from "invoice_items"
  where "invoice_items"."expiry_date" >= [today's date]

This approach should work assuming that existing subscriptions never change (and only new ones with new subscription_id get added).

user_145968560

Apparently, you can't use virtual field to do async operations. Nor can you query based on virtual fields. They are defined entirely in javascript.

So it can be done as follows (using raw imported from Objection.js):

import objection from "objection";
const { Model, raw } = objection;
...

class InvoiceItem extends Model {
  ...
  static modifiers = {
    expiryInfo(qb) {
      qb.select(
        'invoice_items.*', 
        raw('activation_date + make_interval(days => subscription.duration)').as('expiration_date'),
        raw('activation_date + make_interval(days => subscription.duration) < now()').as('expired_bool'),
      ).joinRelated('subscription')
    }
  }
  ...
}

    const query = await User.query()
      .findById(userId)
      .withGraphFetched("invoiceItems(expiryInfo).subscription")
      .withGraphFetched("invoices")

This uses Postgres functions, but the idea remains the same for other databases, though the code inside raw may need to be slightly adjusted.

user_73872690
Related