# How to create more complexe query in weweb? (left join, etc)

**URL:** <https://community.weweb.io/t/how-to-create-more-complexe-query-in-weweb-left-join-etc/9714>\
**Category:** How do I?\
**Created:** [July 14, 2024, 1:20am UTC](https://community.weweb.io/t/how-to-create-more-complexe-query-in-weweb-left-join-etc/9714 "2024-07-14T01:20:08Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![andrei](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@andrei](https://community.weweb.io/u/andrei)\
**Post date:** [July 14, 2024, 1:20am UTC](https://community.weweb.io/t/how-to-create-more-complexe-query-in-weweb-left-join-etc/9714/1 "2024-07-14T01:20:08Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![raydeck](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/raydeck/32/384_2.png) [@raydeck](https://community.weweb.io/u/raydeck)\
**Post date:** [July 14, 2024, 10:01am UTC](https://community.weweb.io/t/how-to-create-more-complexe-query-in-weweb-left-join-etc/9714/2 "2024-07-14T10:01:31Z")

</div>

Weweb itself doesn’t ahve a database functionality, so what database are you trying to query in this manner? SUpabase? Xano?

---

<div class="post-metadata">

**Author:** ![andrei](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@andrei](https://community.weweb.io/u/andrei)\
**Post date:** [July 14, 2024, 10:10am UTC](https://community.weweb.io/t/how-to-create-more-complexe-query-in-weweb-left-join-etc/9714/3 "2024-07-14T10:10:14Z")

</div>

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:

```auto
query{
persons{
name
}
}

```

Have you tried graphql? Is it more performant?

---

<div class="post-metadata">

**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\
**Post date:** [July 14, 2024, 10:38am UTC](https://community.weweb.io/t/how-to-create-more-complexe-query-in-weweb-left-join-etc/9714/4 "2024-07-14T10:38:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![raydeck](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/raydeck/32/384_2.png) [@raydeck](https://community.weweb.io/u/raydeck)\
**Post date:** [July 14, 2024, 2:18pm UTC](https://community.weweb.io/t/how-to-create-more-complexe-query-in-weweb-left-join-etc/9714/5 "2024-07-14T14:18:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Broberto](https://sea2.discourse-cdn.com/flex016/user_avatar/community.weweb.io/broberto/32/5918_2.png) [@Broberto](https://community.weweb.io/u/Broberto)\
**Post date:** [July 14, 2024, 2:21pm UTC](https://community.weweb.io/t/how-to-create-more-complexe-query-in-weweb-left-join-etc/9714/6 "2024-07-14T14:21:15Z")

</div>

> [@raydeck](#):
>
> 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?

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.