WITH persons_organizations AS (
SELECT
p.id AS person_id,
p.organization_id AS person_organization_id,
p.first_name,
p.last_name,
p.name AS person_name,
p.email,
p.title,
p.phone,
p.avatar AS person_avatar,
p.assigned_to AS person_assigned_to,
p.team_id AS person_team_id,
p.appointment AS person_appointment,
p.status AS person_status,
p.candidate_status AS person_candidate_status,
p.candidate_appointment AS person_candidate_appointment,
p.linkedin AS person_linkedin,
o.id AS organization_id,
o.website,
o.status AS organization_status,
o.avatar AS organization_avatar,
o.cover AS organization_cover,
o.phone AS organization_phone,
o.email AS organization_email,
o.tax_id AS organization_tax_id,
o.assigned_to AS organization_assigned_to,
o.team_id AS organization_team_id,
o.name AS organization_name
FROM
persons p
LEFT JOIN
organizations o ON p.organization_id::uuid = o.id
),
person_notes AS (
SELECT
receiver_id::uuid AS person_id,
array_agg(note) AS notes
FROM
notes
GROUP BY
receiver_id
)
SELECT
po.*,
pn.notes
FROM
persons_organizations po
LEFT JOIN
person_notes pn ON po.person_id = pn.person_id
WHERE
po.person_appointment <= CURRENT_DATE + INTERVAL ‘1 day’ - INTERVAL ‘1 second’
ORDER BY
po.person_appointment DESC
LIMIT 100;
Supabase. I found views and I tri to create a view in supabase and in weweb to have only the results. The fact is that reading views is less performant than making the complex query each time. I would like to find something more performant, like writing the query in code. I was looking for graphql, the problem is that I connect to the supabase but it fetch only the persons is not a query in format of:
You can write the joins in WeWeb’s Supabase plugin, but it requires some tinkering actually. You need to use the Advanced field tab. If you’d be interested in diving deeper into this, feel free to DM me and we can arrange something. This is a little bit of a complex topic which could be better explained on a per case basis.
What does performant mean in this context? Are we solving a 5-second problem, a 500ms problem or a 50ms problem that you’re just trying to optimize?
I will say that doing this kind of complex query live is going to be expensive every day of the week. Subqueries, left joins, and an inequality on date in the mix mean that there’s a lot of work to be done every time it goes through. One might look at the data structure you are querying, like whether you really want to have a cache built in front of your relational tables for the purpose of this kind of client fetching.
Probably what they mean is that they’ve read somewhere around the forum me, or someone else stating that views indeed aren’t indexed, so it takes a longer time with huge amount of records to filter them.