Trouble getting query to work with subquery
I'm having trouble getting this query to work in drizzle,
This is what I'm attempting:
Can someone please help me?
SELECT i.*, h1.*
FROM items i
left JOIN (
SELECT h.*
FROM history h
order by h.item_id, h.timestamp desc
limit 2
) h1 ON i.id = h1.item_id
WHERE i.name LIKE '%foo%'
ORDER BY i.name
limit 10;SELECT i.*, h1.*
FROM items i
left JOIN (
SELECT h.*
FROM history h
order by h.item_id, h.timestamp desc
limit 2
) h1 ON i.id = h1.item_id
WHERE i.name LIKE '%foo%'
ORDER BY i.name
limit 10;This is what I'm attempting:
const sq = db
.select()
.from(history)
.where(eq(history.itemId, items.id))
.orderBy(desc(history.timestamp))
.limit(2)
.as('sq')
const itemSearchByNameWithLatestPriceData = db
.select({
item: items,
category: categories,
history,
})
.from(items)
.leftJoin(sq, eq(history.itemId, items.id))
.where(or(like(items.name, placeholder('query')), like(items.name, placeholder('query'))))
.limit(20)
.prepare()
await searchWithPriceData.execute({ query: '%foo%' });const sq = db
.select()
.from(history)
.where(eq(history.itemId, items.id))
.orderBy(desc(history.timestamp))
.limit(2)
.as('sq')
const itemSearchByNameWithLatestPriceData = db
.select({
item: items,
category: categories,
history,
})
.from(items)
.leftJoin(sq, eq(history.itemId, items.id))
.where(or(like(items.name, placeholder('query')), like(items.name, placeholder('query'))))
.limit(20)
.prepare()
await searchWithPriceData.execute({ query: '%foo%' });Can someone please help me?
