Also curious. I’m currently using TanStack Router and I haven’t noticed issues—and this is coming from someone who will stop using sites that break basic browser functionality like the back button
SQL makes it very hard to express real world requirements.
1. No easy way to limit child records count in joins - find all orders with orderproduct.amount is greater than X. Obviously this will genereate duplicates for orders that have more than one such orderproduct. So you slap a distinct on it… but what if you need an aggregation?
The possible fixes are highly non-trivial: subqueries, window functions, or vendor specific: outer apply.
2. Or queries, that is when you group where conditions with OR are very hard (impossible) to optimize.
Apart from the trivial case where the conditions are all on the same column, you are better of leaving the declarative world and imperatively tell sql to do a union.
in 2, looking at your article, from your first query it looks like person_relationship contains both (A,B) and (B,A) for all related people A and B; otherwise the left join won't work. If you also make people related to themselves and store (A,A) and (B,B) there your query becomes much simpler:
SELECT other.id, other.name
FROM person p
JOIN person_relationship r ON r.from_person_id = p.id
JOIN person other ON r.to_person_id = other.id
WHERE p.family_id = @familyId;
Creative solution! Of course this creates more maintenance to ensure such records exist. I guess you kinda proved my point, you have to work around it.
Your own article points out that exists handles the first case. Exists is not actually implemented as a subquery, it is merely syntactically a subquery.
It is time that democracies stop doing business with autocratic regimes. Not just some of them, because if everything is manufactured in china, which keeps doing business with iran and russia, it seems pointless.
At this point it should be obvious to everyone, that western money is (transitively) keeping all the worst regimes alive.
People stopped buying south African stuff, as an apartheid boycott, can we get some china boycott going?
Is Wouter better in this regard?
reply