friendship query that returns the other user object

I have two tables:

friendships:{id:uuid, sender: uuid, target: uuid, status: enum("accepted","pending")}

friendships:{id:uuid, sender: uuid, target: uuid, status: enum("accepted","pending")}

and

profiles:{id:uuid, username:string}

profiles:{id:uuid, username:string}

I want to query the friendships table for the local user id from the supabase client (supabase auth table syncs rows to the profiles table), and return an object like {id:friendship.id, status:friendship.status, user:profile} where the user is the other user, unfortunately most ai coding assistants dont have full or up to date knowledge of drizzle orms (including the one on the drizzle docs site) so is this possible with just drizzle syntax's and how can i do this ?

5 Replies

Aaroned•4mo ago

https://orm.drizzle.team/docs/rqb

! AlexNotTheLion•4mo ago

Not particularly what I was looking for, but thank you for your Insightful response for anyone coming across this in the future, this is the query that i ended up using

const user = data.user;

const queryResult = await db
    .select({
        id: friendships.id,
        status: friendships.status,
        incoming: sql<boolean>`friendship.receiving_user = ${user.id}`,
        profile: {
            id: profiles.id,
            username: profiles.username,
            avatarUrl: profiles.avatarUrl
        }
    }).from(friendships).innerJoin(profiles, sql`(
        friendship.sending_user != ${user.id}
        AND friendship.sending_user = profile.id
        ) OR (
        friendship.receiving_user != ${user.id}
        AND friendship.receiving_user = profile.id
        )`)
    .where(sql`friendship.sending_user = ${user.id} OR friendship.receiving_user = ${user.id}`);

const user = data.user;

const queryResult = await db
    .select({
        id: friendships.id,
        status: friendships.status,
        incoming: sql<boolean>`friendship.receiving_user = ${user.id}`,
        profile: {
            id: profiles.id,
            username: profiles.username,
            avatarUrl: profiles.avatarUrl
        }
    }).from(friendships).innerJoin(profiles, sql`(
        friendship.sending_user != ${user.id}
        AND friendship.sending_user = profile.id
        ) OR (
        friendship.receiving_user != ${user.id}
        AND friendship.receiving_user = profile.id
        )`)
    .where(sql`friendship.sending_user = ${user.id} OR friendship.receiving_user = ${user.id}`);

Aaroned•4mo ago

@! AlexNotTheLion yes using standard select and innerJoin is another option. in your innerJoin and where clauses I'd suggest using the drizzle filter functions instead of raw sql

.where(or(eq(friendship.sending_user, user.id),eq(friendship.receiving_user,user.id)))

.where(or(eq(friendship.sending_user, user.id),eq(friendship.receiving_user,user.id)))

Aaroned•4mo ago

https://orm.drizzle.team/docs/operators

Drizzle ORM - Filters

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

! AlexNotTheLion•4mo ago

updated! thank you kindly 🙂

Gaming

Programming

friendship query that returns the other user object