Nico Posts - Answer Overflow

Nico

Explore posts from servers

•Created by Nico on 10/30/2024 in #help

Help with relations: many-to-many + one-to-one

I'm having trouble figuring out how to model the following relationships between papers, authors, and author's names with Drizzle relations. The schema looks like this (I've ommitted some columns for brevity)

export const authors = sqliteTable("authors", (t) => ({
   authorId: t.integer().primaryKey(),
   firstName: t.text(),
   lastName: t.text(),
}));

export const papers = sqliteTable("papers", (t) => ({
   paperId: t.integer().primaryKey(),
}));

export const authorsToPapers = sqliteTable("authors_to_papers", (t) => ({
   authorId: t.integer().notNull().references(() => authors.authorId),
   paperId: t.integer().notNull().references(() => papers.paperId),
}));

export const authors = sqliteTable("authors", (t) => ({
   authorId: t.integer().primaryKey(),
   firstName: t.text(),
   lastName: t.text(),
}));

export const papers = sqliteTable("papers", (t) => ({
   paperId: t.integer().primaryKey(),
}));

export const authorsToPapers = sqliteTable("authors_to_papers", (t) => ({
   authorId: t.integer().notNull().references(() => authors.authorId),
   paperId: t.integer().notNull().references(() => papers.paperId),
}));

Conceptually, the relations are as follows: - each row in authors can be related to many rows in papers, and each row in papers can be related to many rows authors (many to many) - each row in authors_to_papers is be associated with one row of authors Here are the relations I have set up:

export const authorsRelations = relations(authors, ({ one, many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const papersRelations = relations(papers, ({ many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const authorsToPapersRelations = relations(
   authorsToPapers,
   ({ one }) => ({
      author: one(authors, {
         fields: [authorsToPapers.authorId],
         references: [authors.authorId],
      }),
      paper: one(papers, {
         fields: [authorsToPapers.paperId],
         references: [papers.paperId],
      }),
   }),
);

export const authorsRelations = relations(authors, ({ one, many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const papersRelations = relations(papers, ({ many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const authorsToPapersRelations = relations(
   authorsToPapers,
   ({ one }) => ({
      author: one(authors, {
         fields: [authorsToPapers.authorId],
         references: [authors.authorId],
      }),
      paper: one(papers, {
         fields: [authorsToPapers.paperId],
         references: [papers.paperId],
      }),
   }),
);

I know that I can do the following to return a paper with it's author(s):

await db.query.papers.findFirst({
   with: {
      authorsToPapers: true,
   },
});

await db.query.papers.findFirst({
   with: {
      authorsToPapers: true,
   },
});

But how do I get from the authorIds returned in the output of the above to the firstName and lastName associated with those authorIds?

5 replies

DTDrizzle Team

•Created by Nico on 9/28/2024 in #help

Subqueries in insert statements

I have the following schema (in sqlite)

CREATE TABLE
    languages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE NOT NULL
    );
    
    -- Create JobTitles Table
CREATE TABLE
    job_titles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        language_id INTEGER,
        FOREIGN KEY (language_id) REFERENCES languages (id),
    );

CREATE TABLE
    languages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE NOT NULL
    );
    
    -- Create JobTitles Table
CREATE TABLE
    job_titles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        language_id INTEGER,
        FOREIGN KEY (language_id) REFERENCES languages (id),
    );

When inserting into job_titles, in SQL, I've done the following to get the correct language_id given a language.code:

-- Insert into JobTitles
INSERT INTO
    job_titles (title, language_id)
VALUES
    (
        'Barmanka',
        (SELECT id FROM languages WHERE code = 'pl-PL'),
    );

-- Insert into JobTitles
INSERT INTO
    job_titles (title, language_id)
VALUES
    (
        'Barmanka',
        (SELECT id FROM languages WHERE code = 'pl-PL'),
    );

Is there a way to do this in Drizzle outside of using the sql function and writing the wrong string? Right now, I've just writen a Typescript function to recreate the functionality but I just want to know if there's other approaches

3 replies

Gaming

Programming