Server Side pagination not working with join table

App Building
1

getUserState :
select user_id, DATE(created_on) AS SignUp_Timestamp, user_state as Onboarding_State from user order by DATE(created_on) desc
limit {{ table1.pagination.pageSize }}
offset {{table1.pagination.offset}};

getLookup:
SELECT
user_id,
MAX(CASE WHEN rp_name = "GCC" THEN status END) as EH_Onboarding_Status_GCC,
MAX(CASE WHEN rp_name = "LULU" THEN status END) as EH_Onboarding_Status_LULU
FROM
customer_lookup
WHERE
rp_name IN ("LULU", "GCC")
GROUP BY
user_id
ORDER BY
created_on DESC
limit {{ table1.pagination.pageSize }}
offset {{table1.pagination.offset}};

getKyc:
select user_id, status as kycStatus from user_kyc order by DATE(created_on) DESC
limit {{ table1.pagination.pageSize }}
offset {{table1.pagination.offset}};

And getAllData:
Select * from {{ formatDataAsArray(getUserState.data) }} AS getUser
JOIN {{ formatDataAsArray(getLookup.data) }} AS lookup ON getUser.user_id = lookup.user_id
join {{formatDataAsArray(getKyc.data)}} as kyc on getUser.user_id = kyc.user_id

I am not able to do server side pagination
when I am running getAllData Query getting nothing

2

Hi @Rishivendra_Gupta,

If you look through the first page of results for the individual queries (getUserState, getLookup, and getKyc), do you see any matches? I tested this same set up on my side & the first page for each joined query doesn't have any matches, so I get 0 results in my query json with sql query. When I click to page two, which re-triggers the queries, I do get a result since the second page for each query happens to have a match.

Any chance all 3 queries are using the same resource? If so, I'd recommend combining the joins into one single query with pagination.

3

Hi @Tess I did this by running all query in one resource and I write query using left join.

1 Like