I have two tables: notes and tags, related by note_tags
.createTable('notes', function (table) {
table.increments('id').primary()
table.string('title', 100).notNullable()
table.string('content', 300).notNullable()
table.boolean('archived').notNullable().defaultTo(false)
table.timestamp('updated_at').defaultTo(knex.raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
})
.createTable('tags', function (table) {
table.increments('id').primary()
table.string('name', 20).notNullable()
})
.createTable('note_tags', function (table) {
table.increments('id').primary()
table.integer('note_id').unsigned().notNullable()
table.integer('tag_id').unsigned().notNullable()
table.foreign('note_id').references('notes.id')
table.foreign('tag_id').references('tags.id')
})
I want to get all the "used" tags, the ones that have at least one relation with any note
I don't know much about SQL, and with help of copilot i tried with:
async getUsedTags() {
return await Tag.query()
.select('tags.*')
.join('notes_tags', 'tags.id', 'notes_tags.tag_id')
.join('notes', 'notes.id', 'notes_tags.note_id')
.where('notes.archived', false)
}
but it returns "ER_NO_SUCH_TABLE: Table 'ensolvers-challenge.notes_tags' doesn't exist"