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
?
from In Objection.js, how can I include a virtual column with my query?
No comments:
Post a Comment