How to Implement inner join in ydn-db?

Question

I need to implement an inner join on two tables order_header and order_item based on their ORDER_ID. The equivalent SQL query: SELECT * FROM order_header INNER JOIN order_item ON order_header.ORDER_ID=order_item.ORDER_ID How to implement this in ydn-db?

Answer 1

Use db.scan for table joining. Currently YDN-DB does not have a nice wrapper for joining, but manual joining is not that difficult. Here is joining query for SELECT * FROM Supplier, Part WHERE Supplier.CITY = Part.CITY.

var iter_supplier = new ydn.db.IndexValueIterator('Supplier', 'CITY');
var iter_part = new ydn.db.IndexValueIterator('Part', 'CITY');
var req = db.scan(function(keys, values) {
  var SID = keys[0];
  var PID = keys[1];
  console.log(SID, PID);
  if (!SID || !PID) {
    return []; // done
  }
  var cmp = ydn.db.cmp(SID, PID); // compare keys
  if (cmp == 0) {
    console.log(values[0], values[1]);
    return [true, true]; // advance both
  } else if (cmp == 1) {
    return [undefined, SID]; // jump PID cursor to match SID
  } else {
    return [PID, undefined]; // jump SID cursor to match PID
  }
}, [iter_supplier, iter_part]);

See more detail on Join query article.