Subquery in Where Clause - How can I do this query with Drizzle select?

[tjapa]

Hello. I'm trying to use drizzle to make a query that gets all threads along with the most recent message from each thread.

Using SQL, the query is:

select
	*
from
	threads t
left join "threadMessages" tm on
	tm.thread_id = t.id
where
	tm.created_at = (
	select
		max(created_at)
	from
		"threadMessages"
	where
		thread_id = t.id)
	or tm.created_at is null;

How can I get the same result using "drizzle.select()..."?

[tjapa]

I got a similar result using query.threads.findMany():

      const result = drizzle.query.threads
        .findMany({
           with: {
             threadMessages: {
               orderBy: desc(threadMessages.created_at),
               limit: 1,
             },
           },
         })

But I still haven't managed to using select().

[ghost]

You can achieve the same result by using querybuilder

db.select().from(threads)
.leftJoin(threadMessages, eq(threadMessages.threadId, threads.id))
.orderBy(desc(threadMessages.createdAt))
.limit(1)

[tjapa]

This solution only return one thread. What I want are all threads, each with a message.

[ghost]

My Intent is to join the table threads and threadMessages (using left Join) and use the rank() function to partition the data by threadId and ordering by threadMessage.created_at desc and filter the data with rank = 1.

select 
    threads.*,
    threadMessages.*,
    rank() over (partition by threads.id order by threadMessages.created_at desc)
from threads 
left join threadMessages on threadMessages.threadId  = threads.id;

const query = await db.select({
        ...threads,
        ...threadMessages,
        rank: sql.raw(`rank() over (partition by threads.id order by threadMessages.created_at desc)`)
    }).from(threads).leftJoin(threadMessages, eq(threadMessages.threadId, threads.id));
    
 const result = query.filter(q => q.rank == 1);   

@tjapa hope this is fine

[Angelelz]

Here's how you do this with drizzle:

const sq = db.select({ max: sql`max(${threadMessages.createdAt})` }).from(threadMessages).where(eq(threads.id, threadMessages.threadId));

const query = await db.select()
  .from(threads)
  .leftJoin(threadMessages, eq(threadMessages.threadId, threads.id))
  .where(or(
    eq(threadMessages.createdAt, sq),
    isNull(threadMessages.createdAt)
  ));

This might work. Let me know if it doesn't there might be other ways to do it.

[tjapa]

Works great! Thank you!

[tjapa]

@Angelelz do you know if this is better for performance?

      const sq = fastify.drizzle
        .select({ max: sql`max(${threadMessages.createdAt})` })
        .from(threadMessages)
        .where(eq(threads.id, threadMessages.threadId))

      const query = fastify.drizzle
        .select()
        .from(threads)
        .leftJoin(threadMessages, and(eq(threadMessages.createdAt, sq)))

[Angelelz]

You'll have to profile it, or use explain analyze. Performance depends on too many things.

[tjapa]

I understood. Thank you very much!

[NickCrews]

I had a similar problem that I couldn't find a good solution for (but I did find this thread), so I'm putting it here in case someone else finds it.

I needed to get all users that are also in the blocklist table. eg

SELECT * FROM users WHERE user_id IN (SELECT user_id FROM blocked)

This worked for me:

import { inArray } from "drizzle-orm";

const idsOfBlocked = db
    .selectDistinct({ user_id: blocked.user_id })
    .from(blocked);
const query = db
    .select()
    .from(users)
    .where(
         inArray(users.user_id, idsOfBlocked)
    )