How i can join two queries?

Queries and Resources
1

Hi, i want to join dojo_done and dojo_done_business and both are postgresql queries with the following structures:
image

and I'm running the following transformer script

const larnuDojos = {{dojo_done.data}}
const businessDojos = {{dojo_done_business.data}}
let new_dict = {};
for (var i in businessDojos['created_at']) {
  new_dict[businessDojos['created_at'][i]] = businessDojos['quizes'][i] 
  
}
for (var i in larnuDojos['created_at']) {
    date = larnuDojos['created_at'][i]
  if (new_dict[date]) {
    larnuDojos['created_at'][i] += new_dict[date];
  }
}
 return larnuDojos

but for some reason it throws this exception:

  • message:"Cannot read properties of undefined (reading '0')"

Can someone help me ?

Kind regards,

2

Hi @josseed, welcome to Retool community!

Just to confirm quickly, dojo_done.data and dojo_done_business.data have the same output structure, right?

If that's the case, why not use UNION ALL in your postgres query instead of using a transformer?
i.e.

//Select Query for dojo_done
UNION ALL
//Select Query for dojo_done_business

UNION ALL basically concats all your results from dojo_done and dojo_done_business. Make sure they have the same column structure (e.g. created at is the same index for both queries, same types for the aligned columns).

I hope this make sense.

Regards

1 Like
3

Hi @jocen thanks for your answer!

The problem is that each table comes from different sources (two databases in this case)

4

Hi @josseed, oh i see.

Going back to your transformer, it seems you have a single 'z' on that quizes column for the first for loop.

Anyways, I'm wondering why are you not using .push()? If you want to join them and they have the same output structure, just do:

const larnuDojos = {{dojo_done.data}}
const businessDojos = {{dojo_done_business.data}}

larnuDojos['created_at'].push(...businessDojos['created_at']);
larnuDojos['quizzes'].push(...businessDojos['quizzes']);

return larnuDojos

I used this stackoverflow answer as reference for the script above.

1 Like