Query for Two tables

1

Hi,

I have two table one deal and borrow.deal table contain deal.connection.[list]ofRisk.[list]ofborrower.borrow_id and borrow table contain borrow_id. in request i will send borrow_id i need to write query for get both table full value.

Please share me any idea on this.

2

With a result protobuf message like this:

message DealsForBorrow {
  Borrow borrow = 1;
  repeated Deal deals = 2;
}

You should be able to do a query like this:

SELECT borrow.* as borrow, collect(deal.*) as deals
FROM borrow 
JOIN deal ON borrow.id = ANY(deal.connection.ofRisk.ofBorrower.borrow_id) 
WHERE borrow.id = :borrow_id

Looks like something about that is not working when running locally (with in memory database), it returns an empty result at all times, I’ve created an issue in our internal tracker to look into that. Deployed using actual DB it produces the expected results.

3

We have realized there is a problem also with production, related to joining on fields that are nested several levels down. My first statement about that it works in production is false, we will have to provide a fix in the Kalix runtime for this to work.

4

Thanks, Johanandren. I will check