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
Angelelz14mo 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)))
AdamRackis
AdamRackisOP14mo ago
You're too fast - yep - I discovered that also. Was about to add that info here. Thanks so much!
Want results from more Discord servers?
Add your server