db.exists; and adding another wrapping object around a lateral join

[bitnimble]

Hello! I'm not sure if I just missed it in the documentation, but is there any way to add an object wrapper around properties in a lateral join?

For example, if I have an entity Song that I want to augment with some user-specific properties, such that the final result looks like this:

[
  {
    title: string,
    userProjection: {
      isFavorited: boolean,
      isSomething: boolean,
    },
  },
  ...
]

where the values of isFavorited and isSomething come from other shortcut queries, how would I do this? userProjection is a simple object wrapper and isn't its own query.

I essentially want something like this and I'm not sure if it's possible right now and I'm just not doing it correctly:

await db.select('songs', whereableFoo, {
  lateral: {
    userProjection: {
      isFavorited: db.selectOne('favorites', { map_id: db.parent('id') }),
      isSomething: db.selectOne('someOtherTable', { map_id: db.parent('id') }),
    },
  }
});  

Unrelated to this main issue, but also is there any shortcut method of doing something like exists to check if a row exists? Right now I'm kind of doing a hacky thing where I do a selectOne and then (in regular TS) just rewrite it as !!property to check its nullability, but it's not very nice...

Thanks in advance!

[jawj]

I'm afraid I don't think either of these things is possible as it stands.

• I'm sure the object-wrapper for lateral queries is possible in principle, but it would further complicate the supporting types, and I'm not sure how widely useful it would be. Is there a special reason you need the output to have that specific shape?

• On your second query, am I right in thinking what you're currently doing is along the lines:

const songExists = await db.selectOne('songs', { title: "When I'm Sixty Four" }).run(pool) !== undefined;

That doesn't seem terribly hacky to me ... ? Are you saying you'd prefer a db.exists method that does the undefined check for you?

[bitnimble]

All good! This also isn't really a request to add it, I just wanted to make sure there was nothing super simple that I was just missing!

The reason it has that specific shape is because I have some static data that I'm then augmenting with user-specific data. To add some more context to my example above, I have some data for a song (a title, a track length, etc), and then I have an optional property userProjection?: { ... } that contains a whole bunch of (non-optional) properties inside that, which is hydrated only if there's a logged-in user session.

It would be possible to splat those properties out up into the root of the object like this:

{
  title: string,
  trackLength: number,
  // User-specific properties, present only if logged in
  isFavorited?: boolean,
  userTags?: Set<string>,
  ...
}

but that isn't really an accurate / semantically correct schema to describe what's happening, since this makes it seem possible for isFavorited and userTags to be independently optional, when they should really all be optional or not (it also makes it a lot more annoying in the business logic for the application because then we need separate null checks for each property, rather than just one for userProjection).

I wouldn't say that this is a common case either though, so if this seems like a really niche application to you and it would add a lot of complexity, then I wouldn't bother :)

For the second query, yep that's basically what I am doing, but I'd like it to be done in the lateral join since my first query is returning multiple rows, so it would need to be a sql fragment rather than in JS land. So it would be nice for it to be joined via db.parent('id') instead rather than looping over the entities in JS and doing separate queries for each row later.

Something like this feels like it could be clean (and it's very satisfying to have everything come back in a single query :P)

const songs = await db.select('songs', db.all, {
  lateral: {
    isFavorited: db.exists('favorites', { songId: db.parent('id') }),
  }
});

I think it might be possible via manual sql but I'm not sure... maybe with a select ... case when exists or something, I still have yet to try it myself.

[jawj]

Yeah, OK. I think I'm going to leave the object wrapping as a bit niche. :)

But I'll leave this open and have a bit more of a think about db.exists.