AdamRackis Posts - Answer Overflow

AdamRackis

Explore posts from servers

•Created by AdamRackis on 9/16/2023 in #help

Possible to select* off of Joined table?

Consider this SQL query

SELECT sb.*
FROM books b
LEFT JOIN similar_books sb
ON JSON_SEARCH(b.similarBooks, 'one', sb.isbn)
WHERE b.id = ? AND sb.id IS NOT NULL;

SELECT sb.*
FROM books b
LEFT JOIN similar_books sb
ON JSON_SEARCH(b.similarBooks, 'one', sb.isbn)
WHERE b.id = ? AND sb.id IS NOT NULL;

I have it converted to this Drizzle query, which looks to be correct.

const similarBooksQuery = db
  .select({
    id: similarBooks.id,
    title: similarBooks.title,
    isbn: similarBooks.isbn,
    authors: similarBooks.authors,
    mobileImage: similarBooks.mobileImage,
    mobileImagePreview: similarBooks.mobileImagePreview,
    smallImage: similarBooks.smallImage,
    smallImagePreview: similarBooks.smallImagePreview
  })
  .from(booksTable)
  .leftJoin(similarBooks, sql`JSON_SEARCH(${booksTable.similarBooks}, 'one', ${similarBooks.isbn})`)
  .where(and(eq(booksTable.id, Number(id)), isNotNull(similarBooks.id)))

const similarBooksQuery = db
  .select({
    id: similarBooks.id,
    title: similarBooks.title,
    isbn: similarBooks.isbn,
    authors: similarBooks.authors,
    mobileImage: similarBooks.mobileImage,
    mobileImagePreview: similarBooks.mobileImagePreview,
    smallImage: similarBooks.smallImage,
    smallImagePreview: similarBooks.smallImagePreview
  })
  .from(booksTable)
  .leftJoin(similarBooks, sql`JSON_SEARCH(${booksTable.similarBooks}, 'one', ${similarBooks.isbn})`)
  .where(and(eq(booksTable.id, Number(id)), isNotNull(similarBooks.id)))

My question is, is it possible to just select * from the joined table, without listing out every field? I realize I could probably flip the query around and do a right join, but I don't want to do that; I'd rather keep listing out all the fields if that's my only option. Does Drizzle have a shortcut for this?

5 replies

DTDrizzle Team

•Created by AdamRackis on 9/15/2023 in #help

InferModelFromColumns with columns defined with sql``

Let's say I have a select list that looks like this

const defaultBookFields = {
  id: books.id,
  tags: sql<number[]>`(SELECT JSON_ARRAYAGG(tag) from books_tags WHERE book = \`books\`.id)`.as("tags"),
  subjects: sql<number[]>`(SELECT JSON_ARRAYAGG(subject) from books_subjects WHERE book = \`books\`.id)`.as("subjects"),
  title: books.title
};

const defaultBookFields = {
  id: books.id,
  tags: sql<number[]>`(SELECT JSON_ARRAYAGG(tag) from books_tags WHERE book = \`books\`.id)`.as("tags"),
  subjects: sql<number[]>`(SELECT JSON_ARRAYAGG(subject) from books_subjects WHERE book = \`books\`.id)`.as("subjects"),
  title: books.title
};

This works fine, but if I try to use it with InferModelFromColumns, like so export type FullBook = InferModelFromColumns<typeof defaultBookFields>; I get a TS error - the tags and subjects fields are not Columns, but rather ad hoc SQL results. I do know how to work around this. I could easily Omit<> those two columns, and then manually append a tabs and subjects property of the right type. But is there any way to make this work as is? Absolutely love this library by the way - thank you for the amazing work!

15 replies

Gaming

Programming