0

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"

0 Answers0