Phonograph query: how to join 2 datasets and get distinct

Question

My question concerns Slate + Phonograph end point. Solution can be expressed using ontology and objects creation but my business domain doesn't have permission to use them so I prefer to discuss about Slate + Phonograph.

My questions are at the end, first let me explain the context simplified like this:

1. I sync to foundry the database of a "tickets and actions management system" and I get 2 datasets:
   • one for TICKETS primary key = ticket_number
   • the other for TICKET'S ACTIONS primary key = action_number, secondary key = ticket_number
2. In a Slate application, user can observe charts and higlights about TICKETS and ACTIONS filtered on creteria relative to TICKETS' properties and ACTIONS' properties. For instance let's says displaying count of opened Actions allocated to an urgent Ticket opened last year.

To achive that I have 2 solutions:

1. perform 2 successive phonograph queries:
   • 1st. query on TICKETS filtered on "Urgent Ticket opened last year" => return TICKET NUMBERS
   • 2nd query on ACTIONS filter on TICKET NUMBERS of the previous query
   • (or in the reverse order: 1st ACTION, 2nd TICKET).
2. work with only 1 dataset which is already the "join" of TICKETS and ACTIONS.

My concerns and questions are:

• in the 1st solution I may be required to handle large quantity of TICKET NUMBERS (or ACTION NUMBERS) that exceed 10 000 numbers.
  • Q1: Is it good to ingest them in the second query?
  • Q2: what can I do when TICKET NUMBERS exceed 10 000 numbers?
  • Q3: is there a mean to join queries directely in the backend with phonograph like in SQL (SELECT * from TICKET LEFT JOIN ACTION WHERE x y z)?
• in the 2nd solution count and other KPIs of TICKET are not unique as soon as there is more than one action corresponding to the filter. I can clean the data with a JS function to keep only unique TICKETS but there is a blocking point if I want to display them in table with server-side paggination.
  • Q4: is there a mean to get distinct rows (doc) in phonograph considering only some columns (prop)? Let's says returning one row per TICKET event if several ACTIONS fit the filter.

Best regards.