In WeWeb, I’d like to create a single collection that combines “unstructured” data from three related tables: “contacts,” “company,” and “appointments.”
For example:
I want to include first and last names from the “contacts” table.
I also need company information from the “company” table (e.g. company name).
Additionally, I intend to include job titles (e.g., CEO) from the “appointments” table.
I’ve observed that Supabase supports GraphQL queries, and I believe this feature is perfect for my needs.
Is there someone who has already done this and can help me structure my first GraphQL query in Supabase?
As a side note, do you think you’ll be using information from these 3 tables elsewhere in your app? If yes, to avoid loading the same data multiple times, you might want to create 3 separate collections in WeWeb and use the lookup and rollup formulas to display the data you need, when you need it.
I have indeed explored both the GraphQL query and the option of creating three separate collections, as you suggested. Unfortunately, neither approach has provided the desired result for me in this case.
My GraphQL query
query WorkspaceContacts{
current_workspace_contactsCollection {
edges {
currentContacts: node {
contact {
id
created_at
first_name
last_name
personal_titles {
name
}
appointmentsCollection {
edges {
node {
id
created_at
companies {
name
}
appointment_email {
email
}
appointment_job_titlesCollection {
edges {
node {
job_titles {
id
name
}
}
}
}
}
}
}
}
}
}
}
}
How can I achieve my goal using the SQL collections of the Supabase plugin and lookup and rollup formulas?
Given that each table in my database is expected to contain around 5000 records in the future, I’m particularly concerned about optimizing performance and efficiency.
Considering my specific situation, what would you recommend as the most effective solution? I’m open to any advice or insights you might have to help me achieve the desired outcome.
Could you send here your schemas/definitions? You can find it when you click on your supabase table and [Definition] in the top right.
looks like this, I need to see table names and relations, after that if you have it setup right, I might be able to give you the query to copy and run right away.
In your case, I think the Supabase view approach would probably be the most efficients because the number of records in the appointments table is very likely to increase exponentially and you might not want to fetch all the contacts or all the companies.
That said, if you’d like to explore the lookup approach, you inspired this video
Thanks a lot, @Joyce! Your video you shared was super helpful! I’ve got exactly what I needed now, and it only took a few minutes. Tomorrow, I’m going to load up the database with some data and start playing around with queries. Thanks again for your awesome help!
Edit: If the ultimate efficiency is what you seek, you might want to do it on the backend (DB) side.
Try this, I managed to do something like this on a similar dataset to yours, but I’d need to have the SQL editor under my hands to be able to work on this properly.
I also added company.id so you can then on click reference it either on page change for a detail for example, or in a popup (fetch data).
CREATE OR REPLACE VIEW bro_view AS
SELECT pt.name, c.first_name, c.last_name, c."Status",
json_build_object(
'name', cmp.name,
'id', cmp.id
)
) AS company_details
FROM contacts c
JOIN personal_titles pt ON pt.id = c.personal_title_id
JOIN appointments a ON a.contact_id = c.id
JOIN companies cmp ON cmp a.company_id = cmp.id
GROUP BY e.name
after this you can go from your Supabase plugin in WeWeb, and fetch (and filter/sort) the view.
The output looks like this. (different data, but same structure as you’d need)
As previously mentioned, your video was very helpful and I was able to create exactly what I had in mind. I’m looking to implement a free search field (later on, I’ll also add predefined filters). How do I search both within my contact’s first name and last name as well as within the records displayed through the filterbykey formula?
For example, I would like to be able to type “Jobs” and see my record, but also see it if I typed “Apple.”
If you’re using the SQL we created on the call. You just need to filter it with an OR clause and do something like, if name, contains jobs or company contains apple
