1 reply

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;

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?

Nested relation filter

Similar Threads

Similar Threads

Similar Threads