Relational queries vs (chained) joins—a small comparison

[205g0]

edit &'tl;dr: RQs are way more intuitive than joins but need a ded. relations declaration & are not feature-complete yet (nested where:s), so Ithink I get the point of them a bit more, when i wrote this post initially i wasn't there yet

og post:
Since I just don't get the point of RQs I made this comparison, feel free to extend/educate me, tried RQs again today, fumbled around for hours and couldn't get nested where:s to work. Moved to joins (inner, left, right, outer depending on the query), they were as fast, even faster and called it day (EDIT: not true, my initial join query was wrong, needed right instead of inner joins, so calling the right joins need some experience, is error-prone and needs as much time). What do I miss with RQs?

Don't get me wrong, I find the actual RQ command quite intuitive and the API super well designed (kudos here to the team) but yeah I don't understand why I should use them over chained inner joins

	RQs	(Chained) Joins
allow nested where:s	n	after the last join
always return a flat structure which can be used and sorted right away in a typical table view	not always, depends on in which order you build the query	🍏
return a nested object like Mongo's aggregate()	🍏	n
works natively on PlanetScale	n	🍏
more compact command, less ceremony	n	🍏
intuitive	🍏	n (joins can get hairy)
no extra relations declaration needed even with fk-less dbs like PlanetScale	n	🍏
type-safe	🍏	yes but it's easy to build wrong joins
slight type-hints to build the right query	🍏	n
will go through the db's query optimizer, means the optimizer can handle and change order of joins, if needed and supported	not sure	🍏

[cr101]

What's an example of a chained inner join?

[205g0]

Good q, I updated my og post from inner joins to any kind of joins b/c the type of joins depends on the query.

First a non-nested join, get all comments user with id 1 has ever received:

      .select({ commentText: comments.Text })
      .from(posts)
      .where(eq(posts.userId, 1))
      .rightJoin(comments, eq(comments.postId, post.id))

Now get all comments users ever received. Latter are only users who posted and/or received comments:

      .select({ commentText: comments.Text })
      .from(comments)
      .innerJoin(posts, eq(posts.id, comments.postId))
      .innerJoin(users, eq(users.id, post.userId))