Question: Proper way to approach left join?

[DomThePorcupine]

Hi all,

I have a dumb question here - let's say I've got 2 tables with a 1-1 relationship & overlapping column names (in this case the name and id field):

sqlite> PRAGMA table_info(owners);
0|id|integer|1||1
1|name|varchar(255)|0||0
sqlite> PRAGMA table_info(businesses);
0|id|integer|1||1
1|name|varchar(255)|0||0
2|owner_id|varchar(255)|0||0

The tables are populated with some minimal information:

sqlite> SELECT id, name FROM owners;
1|denodb
sqlite> SELECT id, name, owner_id FROM businesses;
2|db management inc|1
sqlite>

If I want to issue a query to join a business and an owner I could:

const db = new DB("test.sqlite");

const pq = db.prepareQuery(
  "select * from `businesses` left join `owners` on `owners`.`id` = `businesses`.`owner_id`;",
);

const rows = pq.allEntries();

console.log(rows);
// Outputs:
[ { id: 1, name: "denodb", owner_id: "1" } ]

pq.finalize();
...

As you can see we get the name and id from the owners table, but none of the info from the businesses table - so they aren't quite being mapped correctly. It would be possible to issue this query specifying the table in the select statement - but that might lead to cumbersome queries if there are a lot of columns... so my question is:

Is that the proper way to approach this problem:

select `businesses`.`id` as b_id, `owners`.`id` as o_id, ....... from `businesses` left join `owners` on `owners`.`id` = `businesses`.`owner_id`;

which returns the expected:

[ { b_id: 2, o_id: 1, ... } ]

or is there some middleground where makeRowObject looks like:

private makeRowObject(row: Row): O {

    const columns = this.columns();

    const distinctTables = columns.reduce((p, c) => p.add(c.tableName), new Set<string>()).size;

    if(distinctTables === 1) {
      this._rowKeys = columns.map((c) => c.name);
    } else {
      this._rowKeys = columns.map((c) => `${c.tableName}.${c.name}`);
    }

    const obj = row.reduce<RowObject>((obj, val, idx) => {
      obj[this._rowKeys![idx]] = val;
      return obj;
    }, {});
    return obj as O;
}

which would return:

[
  {
    "businesses.id": 2,
    "businesses.name": "db management inc",
    "businesses.owner_id": "1",
    "owners.id": 1,
    "owners.name": "denodb"
  }
]

Anyway thanks for your time and the lib :) Dom

[dyedgreen]

As you observed, if you have columns of the same name, querying the rows as objects will only give you back one of them 😅

The proper solution in this case is to either rename the rows, or to use one of the query methods that return an array, which doesn’t suffer from the problem:

for (const [businessId, businessName, ownerId, ownerName] of query.iter()) {
  // …
}