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?

3 Replies

Angelelz•12mo ago

I similarBooks another table or is this a self join? If it is another table you could simply do

const similarBooksQuery = db
  .select(getTableColumns(similarBooks))
  .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(getTableColumns(similarBooks))
  .from(booksTable)
  .leftJoin(similarBooks, sql`JSON_SEARCH(${booksTable.similarBooks}, 'one', ${similarBooks.isbn})`)
  .where(and(eq(booksTable.id, Number(id)), isNotNull(similarBooks.id)))

Angelelz•12mo ago

https://orm.drizzle.team/docs/goodies#get-typed-table-columns

Goodies - DrizzleORM

Drizzle ORM | %s

AdamRackis•12mo ago

You're too fast - yep - I discovered that also. Was about to add that info here. Thanks so much!

Gaming

Programming

Possible to select* off of Joined table?