Drizzle Team•13mo ago

Help with refactoring a (probably unnecessarily) huge query

the query is attached... too big for messages 🤣

message.txt

27 Replies

MooseOP•13mo ago

i'm sorry for creating this abomination...

Angelelz•13mo ago

Bro...

LittleLily•13mo ago

It seems like most of that query could be written with the query API and a with clause to do all the CTEs using relations instead

Angelelz•13mo ago

Why would you write that with the sql operator and not with the drizzle syntax I don't see anything in there not supported by drizzle

MooseOP•13mo ago

They didn't have union support until recently so the new stuff should cover it then?

Angelelz•13mo ago

Yep The types implementation in the Union is very tight. Let me know if you have any problems Quick flex, I implemented it 😂

MooseOP•13mo ago

almost there! I currently have an issue with media, it's only pulling through a single object. not sure how to get it to pull through an array of the related media. any tips or ideas on further refactoring?

message.txt

Angelelz•13mo ago

Here?

const media = db.$with("media").as(
        db
          .select({
            post_id: postMultimedia.postId,
            media_id: postMultimedia.id,
            media_url: postMultimedia.url,
          })
          .from(postMultimedia)
          .innerJoin(topPosts, eq(postMultimedia.postId, topPosts.post_id))
      )

const media = db.$with("media").as(
        db
          .select({
            post_id: postMultimedia.postId,
            media_id: postMultimedia.id,
            media_url: postMultimedia.url,
          })
          .from(postMultimedia)
          .innerJoin(topPosts, eq(postMultimedia.postId, topPosts.post_id))
      )

You are joining postMultimedia with topPosts, but you are not selecting anything from topPosts Maybe you're missing a where?

MooseOP•13mo ago

i don't understand 🤔 it's essentially a mirror of what i had written initially in sql at this point it's producing an object matching the 'selectObj' variable which i guess is obvious, and it's getting one media object with id and url. problem is i want it to produce an array of media objects

Angelelz•13mo ago

You'll probably need to aggregate it yourself. Or if you want the database to do it for you, you can use json_agg function

MooseOP•13mo ago

ah yeah that makes sense, since i was doing that manually before

Angelelz•13mo ago

const selectObj = {
        id: topPosts.post_id,
        title: topPosts.post_title,
        content: topPosts.post_content,
        createdAt: topPosts.post_created_at,
        ownerId: topPosts.post_owner_id,
        communityId: topPosts.post_community_id,
        user: {
          id: users.id,
          name: users.name,
          avatarUrl: users.avatarUrl,
        },
        reactions: {
          positive: positiveReactions.positive_reactions,
          negative: negativeReactions.negative_reactions,
        },
        userReaction: userReaction.reaction,
        totalComments: totalComments.total_comments,
        media: sql`json_agg(json_build_object('id', ${media.media_id}, 'url', ${media.media_url}))`.mapWith((val) => JSON.parse(val) as { id: number, url: string }[]),
        community: {
          id: postCommunity.community_id,
          title: postCommunity.community_title,
          url_title: postCommunity.community_url_title,
        },
      }

const selectObj = {
        id: topPosts.post_id,
        title: topPosts.post_title,
        content: topPosts.post_content,
        createdAt: topPosts.post_created_at,
        ownerId: topPosts.post_owner_id,
        communityId: topPosts.post_community_id,
        user: {
          id: users.id,
          name: users.name,
          avatarUrl: users.avatarUrl,
        },
        reactions: {
          positive: positiveReactions.positive_reactions,
          negative: negativeReactions.negative_reactions,
        },
        userReaction: userReaction.reaction,
        totalComments: totalComments.total_comments,
        media: sql`json_agg(json_build_object('id', ${media.media_id}, 'url', ${media.media_url}))`.mapWith((val) => JSON.parse(val) as { id: number, url: string }[]),
        community: {
          id: postCommunity.community_id,
          title: postCommunity.community_title,
          url_title: postCommunity.community_url_title,
        },
      }

Try that and let me know

MooseOP•13mo ago

are we able to order that? also, tysm for spending time on this! 🫡

Angelelz•13mo ago

What do you mean? order what?

MooseOP•13mo ago

i'll try just ordering it in the subquery

Angelelz•13mo ago

Oh, I see

MooseOP•13mo ago

when i was doing the raw sql i was ordering like this: .orderBy(({ createdAt, media }) => [desc(createdAt), asc(media.url)])

Angelelz•13mo ago

Yeah, probably better to order it in the with I think this is equivalent to:

const media = db.$with("media").as(
        db
          .select({
            post_id: postMultimedia.postId,
            media_id: postMultimedia.id,
            media_url: postMultimedia.url,
          })
          .from(postMultimedia)
      )

const media = db.$with("media").as(
        db
          .select({
            post_id: postMultimedia.postId,
            media_id: postMultimedia.id,
            media_url: postMultimedia.url,
          })
          .from(postMultimedia)
      )

I'm not sure that join is necessary

MooseOP•13mo ago

😮 how could it not be? oh right i can just use where i guess

Angelelz•13mo ago

Since there is no where, you are joining a table that you are not selecting

MooseOP•13mo ago

not following... we're selecting top 10 from posts, and media that belongs to those posts, hence the join anyways, since i added the media json_agg it's now making me add *every *column as an entry to .groupBy, which i'm sure will not produce the intended result. sorry for being difficult 🤣

Angelelz•13mo ago

I think you got it from here lol

MooseOP•13mo ago

at this point i'm gonna revert lol

Angelelz•13mo ago

No you're not 😂

MooseOP•13mo ago

i don't understand this 🤔 i'm new to json functionality in postgres so not sure why it's giving me an error like this for every column now. PostgresError: column "top_posts.id" must appear in the GROUP BY clause or be used in an aggregate function

Angelelz•13mo ago

I wrote that query from memory, might have issues. I can't test right now in a database but I can suggest you run a findMany query with a related table. Make sure you pass {logger: true} to drizzle so you can see the query it produces And then use it as guide

MooseOP•13mo ago

idk what's funny about that, this is way more complex than just writing raw sql lmao

Gaming

Programming

Help with refactoring a (probably unnecessarily) huge query