Combine two queries into one

💬 App Building
1

I have two separate database queries in my Retool app.

One query, getFreelancers, fetches all freelancer records, and another query, getPayments, retrieves all payments made to these freelancers.

Both queries have been transformed with formatDataAsArray.

For example, getFreelancers returns:
[ { "id": 1, "name": "Alice" }, { "id": 2, "name": "Bob" }, { "id": 3, "name": "Charlie" } ]

and getPayments returns:
[ { "freelancer_id": 1, "amount": 500 }, { "freelancer_id": 1, "amount": 200 }, { "freelancer_id": 2, "amount": 300 }, { "freelancer_id": 3, "amount": 400 }, { "freelancer_id": 3, "amount": 100 } ]

I need to combine both queries to get all the freelancers along with their total payments into a single dataset that can be fed into a table.

What is the best/easiest way of doing this?

1 Like
2

Hey there @jose_viera, and welcome to the forum.

There are several ways you can achieve this, depending on how you want to use your data. I asked Chatgpt to provide 3 options, SQL to combine the queries or JAvascript/Lodash to merge them.

These are things that ChatGPT can provide you really cool solutions. Let me know if you need any further input.

Cheers!

SQL Approach (Using JOIN & SUM)

If both tables exist in the same database, you can join them using LEFT JOIN and aggregate the payments using SUM.

sql

CopyEdit

SELECT 
    f.id, 
    f.name, 
    COALESCE(SUM(p.amount), 0) AS total_payments
FROM freelancers f
LEFT JOIN payments p ON f.id = p.freelancer_id
GROUP BY f.id, f.name
ORDER BY f.id;
  • LEFT JOIN ensures all freelancers are included, even if they have no payments.
  • SUM(p.amount) calculates the total payments per freelancer.
  • COALESCE(SUM(p.amount), 0) ensures that freelancers with no payments show 0 instead of NULL.
  • GROUP BY f.id, f.name groups data by freelancer.

JavaScript Approach (Using Lodash or Vanilla JS)

If getFreelancers.data and getPayments.data exist separately in Retool, you can merge them using JavaScript.

Vanilla JavaScript Solution

javascript

CopyEdit

const freelancers = getFreelancers.data;
const payments = getPayments.data;

// Create a mapping of freelancer payments
const paymentMap = payments.reduce((acc, { freelancer_id, amount }) => {
  acc[freelancer_id] = (acc[freelancer_id] || 0) + amount;
  return acc;
}, {});

// Merge with freelancers
const result = freelancers.map(f => ({
  id: f.id,
  name: f.name,
  total_payments: paymentMap[f.id] || 0
}));

result;

Lodash Approach

If you have Lodash in Retool, you can use _.groupBy and _.sumBy for a more declarative solution:

javascript

CopyEdit

const freelancers = getFreelancers.data;
const payments = getPayments.data;

// Group payments by freelancer_id
const groupedPayments = _.groupBy(payments, "freelancer_id");

// Map freelancers and sum their payments
const result = freelancers.map(f => ({
  id: f.id,
  name: f.name,
  total_payments: _.sumBy(groupedPayments[f.id] || [], "amount") || 0
}));

result;

Which One Should You Use?

  • Use the SQL query if both datasets come from the same database → it's more efficient.
  • Use the JavaScript method if getFreelancers and getPayments are separate queries → Retool transformers can handle this easily.

Let me know if you need adjustments! :rocket:

3

Probably the easiest and cleanest way is to use a transformer. Basically, you write a piece of javascript code that takes the results of both queries, merges them, and then you can pass it to your table as a data source.

To do it, on the left panel, click on "code" and then the plus symbol, and select "Transformer" from the dropdown.

Then, write the logic that will merge the queries. Something along these lines, depending on what you're trying to achieve:

const freelancers = getFreelancers.data || [];
const payments = getPayments.data || [];

// Map over each freelancer and compute their total payments
const result = freelancers.map(freelancer => {
  // Filter payments that belong to the current freelancer based on matching id
  const freelancerPayments = payments.filter(payment => payment.freelancer_id === freelancer.id);
  
  // Sum the payment amounts for this freelancer
  const totalPayments = freelancerPayments.reduce((sum, payment) => sum + payment.amount, 0);
  
  // Return a new object combining freelancer details with totalPayments
  return { ...freelancer, totalPayments };
});

return result;

Once you have it, on your table, just select the transformer as your data source. And that should do it.

Hope it helps.