friendship query that returns the other user object

A! AlexNotTheLion4/2/2024
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 ?
A! AlexNotTheLion4/4/2024
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}`);
AAaroned4/4/2024
@! 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)))
AAaroned4/4/2024
Drizzle ORM - Filters
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
A! AlexNotTheLion4/6/2024
updated! thank you kindly 🙂

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
Issue with push commandHello. I pushed migrations to database using `drizzle-kit push:mysql` and It worked fine. Running itDrizzleKit Studio TypeError: customResultMapper is not a functionI am trying to get DrizzleKit studio to run with Cloudflare D1. I already saw the existing workarounpush:mysql autoincrement errorWhen running `push:mysql`, drizzle-kit throws this error: ``` Warning You have removed the primaryHow to search the docs for the correct datatype returned by each function?Since I'm using typescript, quite often I am welcomed by eslint errors. For eample: ```ts try { Self-Relation in Drizzle ORM?I'm trying to create a folder/file structure in postgresql with drizzle orm I have the following tabDrizzle spontaneously trying to truncate my dbToday I opened up Drizzle Studio, connected to my Vercel Postgres database, and a modal popped up sastring arrayHow are string arrays defined in drizzle? It always seems to revert back to json/jsonb when definingdates in sqlite not being evaluated, causing "Invalid Date"``` expiresAt: int("expires_at", { mode: "timestamp" }) .notNull() .$defaultFn(() =>drizzle-kit generate migration for pg is stuck at: Reading config file 'drizzle.config.ts'Hello, im using Deno 1.41.3. After executing `deno run -A npm:drizzle-kit generate:pg` cli just showHow do I reuse the results from the where clause within the findMany?I have a vendor ID and want to retrieve all restaurant IDs and menu IDs associated with that vendor.drizzle kit generate schemas for mysql had a little type mismatched problem.Has anyone encountered a similar problem of mismatched types with the error message "MySqlVarCharBuiHow to make a text based timestamp type for sqlite with zod schema inferenceWhen I use the built in integer with mode timestamp, the generated zod schemas correctly declare thaWhat's the best way to query my db?I'm running Supabase Postgres locally with Drizzle. In production, I'd use the connection pooler wHow to query many-many with mysqlI setup a simple example to test if many-many query and ran again into the same issue. ```ts Error:How do I access config in fromDriver and toDriver when defining a custom type using customType?None of the examples do this so it's not clear how/if this is possible?Cannot get user roleI'm using Next.js with next auth and the drizzle adapter. I must be doing something wrong because th