Julian Comments - Answer Overflow

Julian

Posts Comments

DTDrizzle Team

•Created by Julian on 5/18/2024 in #help

How to use db.query.comments.findFirst with WHERE on comments.post.auhor.name?

https://github.com/drizzle-team/drizzle-orm/discussions/2316

10 replies

DTDrizzle Team

•Created by Julian on 5/18/2024 in #help

How to use db.query.comments.findFirst with WHERE on comments.post.auhor.name?

Ah found it

10 replies

DTDrizzle Team

•Created by Julian on 5/18/2024 in #help

How to use db.query.comments.findFirst with WHERE on comments.post.auhor.name?

Yeah exactly, thank you so much for the help! I've heard there's new ideas for RQB going on for next major versions, excited to see what they come up with!

10 replies

DTDrizzle Team

•Created by Julian on 5/18/2024 in #help

How to use db.query.comments.findFirst with WHERE on comments.post.auhor.name?

I see, thank you so much for help @Sillvva !!! I think the first solution is nicer to write than the alternative solution. I think the alternative solution has an issue if it gets even more complicated. Let's say there's likes table, one-to-many to comments, with userId and commentId . Now if I want to pull likes as well. The first approach is pretty easy to modify.

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      // from here
      exists(
        db.select()
          .from(posts)
          .innerJoin(users, eq(posts.authorId, users.id))
          .where(
            and(
              eq(posts.id, table.postId),
              eq(users.name, postAuthorNameToFind)
            )
          )
      )
      // to here
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    likes: {
      columns: {
        id: true,
        userId: true,
        createdAt: true
      }
    }
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind
            name: true,
          }
        }
      }
    }
  } 
})

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      // from here
      exists(
        db.select()
          .from(posts)
          .innerJoin(users, eq(posts.authorId, users.id))
          .where(
            and(
              eq(posts.id, table.postId),
              eq(users.name, postAuthorNameToFind)
            )
          )
      )
      // to here
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    likes: {
      columns: {
        id: true,
        userId: true,
        createdAt: true
      }
    }
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind
            name: true,
          }
        }
      }
    }
  } 
})

Second approach is probably harder, you probably have to do join with likes table, and then reconstruct the object to make it return the object similarly to the first approach. Hopefully there are more features coming for this in the future, again, thank you so much!

10 replies

DTDrizzle Team

•Created by Julian on 5/18/2024 in #help

How to use db.query.comments.findFirst with WHERE on comments.post.auhor.name?

I can think of something like this but got stuck from here

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      eq(WhatDoIPutHere, postAuthorNameToFind),
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind (Julian)
            name: true,
          }
        }
      }
    }
  } 
})

const textToFind = "im a comment";
const postAuthorNameToFind = "Julian";

db.query.commments.findFirst({
  where: (table, { eq }) =>
    and(
      eq(commments.text, textToFind),
      eq(WhatDoIPutHere, postAuthorNameToFind),
    )
  ),
  columns: {
    id: true,
    text: true,
    createdAt: true
  },
  with: {
    post: {
      with: {
        author: {
          columns: {
            // Need to match this with postAuthorNameToFind (Julian)
            name: true,
          }
        }
      }
    }
  } 
})