Subquery with hasMany chained to belongsTo returns single record

[mordechaim]

Noticing this BREAKING CHANGE, it now returns a single record even when chaining hasMany -> hasOne:

db.book.find(1).select({
   bios: q.authors.chain('bio').select('id', 'description') // should return an array; returns a single record
})

[NelsonMK]

try this

db.book.find(1).select({
    authorIds: q.authors.pluck('id') // will get all authorIds
})

[mordechaim]

@NelsonMK The code snippet was wrong. I updated the example.

[NelsonMK]

Seems the all method is not being applied by default. May be a bug.

Try this @mordechaim

db.book.find(1).select({
   bios: q.authors.chain('bio').select('id', 'description').all() // manually add .all()
})

[romeerez]

@mordechaim in orchid-orm 1.48 the chain is re-implemented and now it should work as expected. chain docs, breaking changes.

db.book.find(1).select({
   // here authors is clearly a "many" association
   bios: q.authors.chain('bio').select('id', 'description')
})

As long as there is a "many" (hasMany, hasAndBelongsToMany) in the chain, it is going to return array. Otherwise it returns a single record.

[mordechaim]

Wow @romeerez , as always you rock!

[mordechaim]

Curious, what's the benefit of using row_number() window function over distinct(id)?

[romeerez]

Curious, what's the benefit of using row_number() window function over distinct(id)?

For instance, let's imagine book stores that have many books, every book has a single author.

db.bookStore.select({
  authors: q => q.books.chain('author')
})

It means getting an author per every book. What if there are two different books written by the same author?

I could use distinct internally, and it would always give you only unique records, and you can argue it make sense to do by default, but I'm not sure if you always want that to be done implicitly and by default.

Therefore, it means getting X for every Y, but not getting a unique set of X joined via the Y.

This is a good and important question, I should give it some more thought, because you might expect it to return unique records. The docs should highlight this, and there should be an easy way to get unique records if that's what you want.

Perhaps, I should add chainDistinct. Maybe distinct should be the default.

Not so long ago it was distinct by nature, it was relying on where exists, the example above used to mean "select authors where exists (select books that belong to this store and to the authors)". I re-implemented that to support limit, offset, ordering, so you can select top 5 authors for the book store ordered by book titles or anything. And now that you brought up distinctiveness I'm in doubts.

[mordechaim]

If row number doesn't enforce distinctiveness, then why is it there in first place? This was how I interpreted partitioning by id and selecting the first row.

I thought it's for this purpose. What really happens if 2 books were written by the same author? Will I get duplicates?

[romeerez]

Apologies for the confusion, I tried the following experiment and can confirm your interpretation is correct.

I created multiple books, only 2 authors, the result is 2 authors - no duplication.

Did I overcomplicate this with row_number when distinct was enough? That's possible.

Experiment's code for books and authors

class BookStoreTable extends BaseTable {
  readonly table = 'bookStore';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
  }));

  relations = {
    books: this.hasMany(() => BookTable, {
      columns: ['id'],
      references: ['bookStoreId'],
    }),
  };
}

class BookTable extends BaseTable {
  readonly table = 'book';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    bookStoreId: t.name('bookStoreId').integer(),
    authorId: t.name('authorId').integer(),
  }));

  relations = {
    author: this.belongsTo(() => AuthorTable, {
      columns: ['authorId'],
      references: ['id'],
    }),
  };
}

class AuthorTable extends BaseTable {
  readonly table = 'author';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
  }));
}

const db = orchidORM(
  config,
  {
    bookStore: BookStoreTable,
    book: BookTable,
    author: AuthorTable,
  },
)

const bookStoreId = await db.bookStore.get('id').create({});
const authorId = await db.author.get('id').create({});
const anotherAuthorId = await db.author.get('id').create({});

// 5 books with the same author
await db.book.insertMany([
  { bookStoreId, authorId },
  { bookStoreId, authorId },
  { bookStoreId, authorId },
  { bookStoreId, authorId },
  { bookStoreId, authorId },
]);

// 1 more book with a different author
await db.book.insert({ bookStoreId, authorId: anotherAuthorId });

const query = db.bookStore.select({
  authors: (q) => q.books.chain('author'),
});

// can confirm this is the SQL we're discussing: it has the row_number()
console.log(query.toSQL());

const result = await query;

/**
* [
*   { authors: [ { id: 13 }, { id: 14 } ] }
* ]
*/
console.dir(result, { depth: null });