Keeping track of what messages have been sent to other users is crucial. I recommend creating a table called phrases_sent to maintain a record of all sent phrases. Make sure to include the phrase_id in this table to establish a relationship.
For guidance on associations, refer to the documentation at this link.
To retrieve phrases that have not been sent yet, you can execute a query that outer joins the phrases_sent table with the phrases table.
Here's an example:
const unusedPhrases = await sequelize.models.phrase.findAll({
where: {
'$phrasesSents.phrase_id$': {[Op.is]: null}
},
attributes: ['phrase.phrase'],
include: [
{
attributes: [],
model: sequelize.models.phrasesSent,
required: false,
}
],
raw: true,
nest: true
});
This query will result in:
SELECT "phrase"."phrase"
FROM "phrases" AS "phrase"
LEFT OUTER JOIN "phrases_sent" AS "phrasesSents" ON "phrase"."id" = "phrasesSents"."phrase_id"
WHERE "phrasesSents"."phrase_id" IS NULL;
Using a LEFT OUTER JOIN is generally more efficient than using a NOT IN (SELECT ...) approach for performance reasons.