Query multiple tables, combined pagination

Allen Tullett

Hi all,

I have three tables, each with different columns and datasets. I would like to be able to:
a) Return all records within each table, combined into a single array of records with pagination. This is so I can page through ALL records regardless of their type
b) Pass a query to each table, and then have a combined array of records returned including pagination for the combined array

Is this possible?

I can see how to fire off three separate queries and then combine results into one array, but the problem with this is that each one then have their own pagination, totals, etc, not one combined

I appreciate any help you can provide.

Unknown

If it's always the same three tables, I'd just loop each call - something like this: 

1. Create a new variable "output" which is an empty array
2. Query your first table
3. For each result, loop and add it to the end of the array 
4. Query your second table
5. For each result, loop and add it to the end of the array 
6. Query your third table
7. For each result, loop and add it to the end of the array 

Then from there, sort your array however you see fit. 

For pagination, you could create a new "results" table, add these results, then re-query them. all in the same automation. 

I'm concerned though about your overall database structure and if it's built with best practices in mind. I don't know your scenario but it might be that you have a Minivan table, a Pickup Truck table, and a Sedan table. In this case you'd want to make a table of all vehicles, then each item links to one in the appropriate other table. So vehicle #1 is pickup truck #1... vehicle #2 is sedan #1, etc. 

Then you would just query the vehicles table, if you're feeding it all in one list to the end user. 

Michael Udinski

You can use the merge filter to merge arrays together - this way you don't need to use a loop on each query.

I'd be mindful of how much data you are putting together. If you have many many records in each table then you might have a tough time querying all records from each table. You are also going to have data that might seem duplicate (i.e. the same id values in multiple tables) - there's definitely some nuance to think through here if you do want to go down this route. 

Ray Deck

At a top level, you can absolutely set up an endpoint that will query multiple tables. Further, you can introduce pagination to such a setup. So the first answer to your question is "yes you can".

The how will depend on the structure and relationships among your data.  makes good points about the nuance.  offers a decent starting point. If you share more about the data structures, you can get more actionable ideas.

If you want a more confidential 1-1 look, this kind of 1-1 mentoring is what I do, and you can book me for a consulting session.