Kysely•10mo ago

Querying on jsonArrayFrom

Hi. So i need to fetch posts with comments from a database that are written by a set group of authors and have comments that are written by a set group of people. How would I accomplish this with sql? The best thing I can think of is fetch all posts and comments where the posts are written by the authors, and then do further clientside filtering on the comments - but is there a way to do this with the sql itself?

3 Replies

oof2win2OP•10mo ago

Another option that I can think of is get post IDs from comments that match the desired comment author IDs and then get the posts from those - but there might be hundreds if not thousands of comments with the IDs the second solution would probably be something like

select * from posts
  where posts.id in (
    select id from comments where comments.id in (PROVIDED_COMMENTER_LIST)
  ) and post.authorId in (PROVIDED_AUTHOR_LIST)
)

select * from posts
  where posts.id in (
    select id from comments where comments.id in (PROVIDED_COMMENTER_LIST)
  ) and post.authorId in (PROVIDED_AUTHOR_LIST)
)

like the only issue i see here is that the following subquery might have a lot of results (and might be say 20% of the comments table in some cases)

posts.id in (
    select id from comments where comments.id in (PROVIDED_COMMENTER_LIST)
  )

posts.id in (
    select id from comments where comments.id in (PROVIDED_COMMENTER_LIST)
  )

koskimas•10mo ago

You shouldn't worry about things like that unless you've measured performance problems. All modern databases have a query optimization phase that is usually able handle cases like this. The actual executed code most likely doesn't create a temporary array with 20% of the table's rows, but instead turns that subquery into a join or a semijoin.

oof2win2OP•10mo ago

okay that makes sense, thanks 👍

Gaming

Programming

Querying on jsonArrayFrom