Ahmed•4w ago

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 😄

4 Replies

Convex Bot•4w 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!

erquhart•4w ago

Is the search index because you want the user to be able to type to narrow down to specific workspaces by name? As long as user would not generally be expected to be the owner of many hundreds of workspaces, and also probably not even be a member of many hundreds of workspaces, I would: - use an index to get a list of all workspaces the user is a member of - map over the list and get the workspaces - filter out the ones where the user is owner Given the things you mentioned around pagination and aggregation I'm guessing I'm missing something. Like are you trying to do this for many users at once in a single query?

AhmedOP•4w ago

Hi there, thanks for your answer! The key requirements are: - The search should allow finding workspaces by their name - Results should be paginated - The search results should include workspaces where the user is either a member without being the owner OR is the owner depending on an argument passed to the query. While we don't anticipate users being part of hundreds of workspaces, the system should be built to handle that possibility if it occurs and we have exactly the same functionality involving other tables. We're also using aggregate instead of collect like convex suggests for large data.

erquhart•4w ago

Even with a large amount of data in your database, the query you're describing would work well with just paginating through the user's workspaces using a regular index and filtering out the ones you don't want with javascript - I would personally just handle search in js as well instead of trying to make search index work here. I'd expect solid performance and scalability with that approach, and it's simple to reason about.

We're also using aggregate instead of collect like convex suggests for large data.

Curious where specifically this was mentioned

Help with search in join table

Did you find this page helpful?