Filtering data based on relationship value
Hello, I'm using subqueries to fetch and return related data from a resource table. I am using the
jsonObjectFrom helper which is working perfectly. My question now is how can I filter the final data based on a field present in the relation data. For example if my main table is User and the related data is Account, how can I fetch Users where the account is active all in the same query? Im already sing the expression builder (eb) for the subquery, do I need to add a separate join expression to be able to filter the data on the related data? Or does Kysely have a pattern/util to make this much simpler?2 Replies
Hey 👋
You could probably, instead of using the helper in the direct select, inner join with a subquery that selects the helper and filter by active. Thus users that don't have an active account will be naturally filtered out, and you still have nested json in the select.
That wouldn't be able to use indexes
The filters would filter by stuff in a JSON blob. The query optimizer couldn't figure out that the stuff came from a place that had indexes