6 replies

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

Convex Community•17mo ago•

6 replies

Ahmed

Help with search in join table

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

Continue the conversation

Join the Discord to ask follow-up questions and connect with the community

Convex Community

Join the Convex Discord! Explore Convex, the backend platform that keeps your app in sync.

15,960 Members

Join

Help with search in join table

Help with search in join table

Continue the conversation

Convex Community

Continue the conversation

Convex Community

Similar Threads