Gary, el Pingüino Artefacto•3mo ago

Nested relation filter

Hi, I'm using Convex for a new side project (mostly for learning so nothing serious) and I'm making a table that have some columns that need to be computed based on relations. For example: Table with users and the total of posts and the total of comments. The admin should be able to filter based on the number of comments. I made initially using the .paginate and then computed the values but what happen when need to filter based on 100+ comments, or 100 comments marked as spam, or 100 comments marked as spam this month on posts related to "Convex" for example? I know about the denormalization method or the recently new aggregate component, but both seems to get really complex that some point. With SQL will be like:

SELECT p.id, p.title, COUNT(c.id) AS spam_comment_count
FROM posts p
JOIN comments c ON c.post_id = p.id
WHERE p.title ILIKE '%convex%'  -- related to "Convex"
  AND c.is_spam = TRUE          -- marked as spam
  AND c.created_at >= date_trunc('month', CURRENT_DATE) -- this month
GROUP BY p.id, p.title
HAVING COUNT(c.id) >= 100       -- at least 100 spam comments
ORDER BY spam_comment_count DESC;

SELECT p.id, p.title, COUNT(c.id) AS spam_comment_count
FROM posts p
JOIN comments c ON c.post_id = p.id
WHERE p.title ILIKE '%convex%'  -- related to "Convex"
  AND c.is_spam = TRUE          -- marked as spam
  AND c.created_at >= date_trunc('month', CURRENT_DATE) -- this month
GROUP BY p.id, p.title
HAVING COUNT(c.id) >= 100       -- at least 100 spam comments
ORDER BY spam_comment_count DESC;

I know, this is inefficient AF and will take may be some seconds to resolve but is at least possible. I don't know if may be there is another way without using denormalization or the aggregate component?

1 Reply

Convex Bot•3mo ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

Nested relation filter

Did you find this page helpful?