Ahmed Posts - Convex Community

Ahmed

•Created by Ahmed on 12/14/2024 in #support-community

Help with search in join table

Hi, I've been looking for an answer for this with no success. I have 3 tables, users, workspaces and members. members is a join table, each member has a userId and a workspaceId. Every workspace has also an ownerId. I want to be able to search (text search withSearchIndex) workspaces where the user is a member of without being the owner. I'm using aggregate for performance reasons and i need pagination. Here is how we would write it in SQL code to be more clear.

SELECT w.*
FROM workspaces w
JOIN members m ON w.id = m.workspace_id
WHERE m.user_id = :user_id
AND w.owner_id != :user_id
AND w.name LIKE :search_term
ORDER BY w.name
LIMIT :limit OFFSET :offset;

SELECT w.*
FROM workspaces w
JOIN members m ON w.id = m.workspace_id
WHERE m.user_id = :user_id
AND w.owner_id != :user_id
AND w.name LIKE :search_term
ORDER BY w.name
LIMIT :limit OFFSET :offset;

Many thank 😄

7 replies