Drizzle Team•2y ago

Help with refactoring a (probably unnecessarily) huge query

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

message.txt

27 Replies

MooseOP•2y ago

i'm sorry for creating this abomination...

Angelelz•2y ago

Bro...

LittleLily•2y 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•2y 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•2y ago

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

Angelelz•2y 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•2y 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•2y 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•2y 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•2y 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•2y ago

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

Angelelz•2y 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•2y ago

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

Angelelz•2y ago

What do you mean? order what?

MooseOP•2y ago

i'll try just ordering it in the subquery

Angelelz•2y ago

Oh, I see

MooseOP•2y ago

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

Angelelz•2y 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•2y ago

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

Angelelz•2y ago

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

MooseOP•2y 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•2y ago

I think you got it from here lol

MooseOP•2y ago

at this point i'm gonna revert lol

Angelelz•2y ago

No you're not 😂

MooseOP•2y 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•2y 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•2y 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

Did you find this page helpful?