7 ms·

A huge fraction (not 100%, but maybe 80%) of my frustration in trying to get technical people to use a database is that they have such a hard time understanding

by pjs_ 2y ago

A huge fraction (not 100%, but maybe 80%) of my frustration in trying to get technical people to use a database is that they have such a hard time understanding JOINs. People endlessly want hacks, workarounds, and un-normalized data structures, for a single reason - they don't want to have to think about JOIN. It's not actually for performance reasons, it's not actually for any reason other than it's easier to imagine a big table with lots of columns. I'm actually sympathetic to that reticence but what I am not sympathetic about is this: why, in 2024, can't the computer figure out multi-table joins for me? Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables. Like say I have apples in boxes in houses. I have an apple table, a box table, and a house table. apples have a box_id, boxes have a house_id. Now I want to find all the apples in a house. Do two joins, cool. But literally everyone seems to write this by hand, and it means that crud apps end up with thousands of nearly identical queries which are mostly just boringly spelling out the chain of joins that needs to be applied. When I started using SQLAlchemy, I naively assumed that a trivial functionality of such a sophisticated ORM would be to implement `apple.join(house)`, automatically figuring out that `box` is the necessary intermediate step. SQLAlchemy even has all the additional relationship information to figure out the details of how those joins need to work. But after weeks of reading the documentation I realized that this is not a supported feature. In the end I wrote a join tool myself, which can automatically find paths between distant tables in the schema, but it seems ludicrous to have to homebrew something like that. I'm not a trained software engineer but it seems like this must be a very generic problem -- is there a name for the problem? Are there accepted solutions or no-go-theorems on what is possible? I have searched the internet a lot and mostly just find people saying "oh we just type out all combinatorially-many possible queries"... apologies in advance if I am very ignorant here

rawgabbit 2y ago
Most databases have the concept of foreign keys. You declare how the tables relate to each other. You can then write a script that queries this metadata to write the join for you. I did this sort of thing over twenty years ago.
- Spivak 2y ago
  Most ORMs have this feature as well and sorry to say you will hit edge cases where the join is ambiguous and have to manually specify it pretty fast.
  - KronisLV 2y ago
    > where the join is ambiguous Join table that maps to an entity in the middle. You can even have multiple columns that have foreign keys against various tables, like some_table_id, other_table_id, another_table_id with only the needed ones being filled out. And in practice, this will be way more manageable than the dynamic mess of the OTLT pattern (table_name and table_id): https://www.red-gate.com/simple-talk/blogs/when-the-fever-is-over-and-ones-work-is-done/ https://www.red-gate.com/simple-talk/blogs/when-the-fever-is... It's not like you have to particularly care about the fact that most of those columns will be empty in practice, as opposed to making your database hard to query or throwing constraints aside altogether.
    - Terr_ 2y ago
      > Join table that maps to an entity in the middle. I'm not not sure what you mean. Are you saying that instead of FK/PK relations like: user.residence_country_id = country.id plus user.citizenship_country_id = country.id You would reify every edge like: user.residence_link_id = link_user_residence.user_id link_user_residence.country_id = country.id user.citizenship_link_id = link_user_citizenship.user_id link_user_citizenship.country_id = country.id Even then, the reverse "I have a country gimme a user" request is ambiguous.
      - Izkata 2y ago
        GP misunderstood what an "ambiguous join" is. Rather than a join that returns multiple results, they're more like when you have relationships like this [0]: user.residence1 -> country user.residence2 -> country user.join(country) You have to specify somewhere which foreign key to follow, it can't be figured out automatically. GP's suggestion doesn't work because there's no way to automatically determine which one to use. I'm only really familiar with Django, and it handles this type of thing by making you specify the "residence1" name instead of the table name "country". [0] To refer back to the top of this comment chain, that user is complaining they can't do something like also have a "country->continent" relationship and do "user.join(continent)" and have the system figure out the two joins needed.
big_whack 2y ago
It's not really a problem of there being combinatorially many ways to join table A to table B, but rather that unless the join is fully-specified those ways will mostly produce different results. Your tool would need to sniff out these ambiguous cases and either fail or prompt the user to specify what they mean. In either case the user isn't saved from understanding joins.
- halfcat 2y ago
  > ”but rather that unless the join is fully-specified those ways will mostly produce different results.” As a SQL non-expert, I think this is why we are averse to joins, because it’s easy to end up with more rows in the result than you intended, and it’s not always clear how to verify you haven’t ended up in this scenario.
  - big_whack 2y ago
    Sorry, but someone who is averse to joins is not a non-expert in SQL, they are a total novice. The answer is like any other programming language. You simply must learn the language fundamentals in order to use it.
    - halfcat 2y ago
      Not sorry, I’ll stick with SQL non-expert as I’ve only worked with databases for a few decades and sometimes run into people who know more. Working with a database you built or can control is kind of a simplistic example. In my experience this most often arises when it’s someone else’s database or API you’re interacting with and do not control. An upstream ERP system doesn’t have unique keys, or it changes the name of a column, or an accounting person adds a custom field, or the accounting system does claim to have unique identifiers but gets upgraded and changes the unique row identifiers that were never supposed to change, or a user deletes a record and recreates the same record with the same name, which now has a different ID so the data in your reporting database no longer has the correct foreign keys, and some of the data has to be cross-referenced from the ERP system with a CRM that only has a flaky API and the only way to get the data is by pulling a CSV report file from an email, which doesn’t have the same field names to reliably correlate the data with the ERP, and worse the CRM makes these user-editable so one of your 200 sales people decides to use their own naming scheme or makes a typo and we have 10 different ways of spelling “New York”, “new york”, “NY”, “newyork2”, “now york”, and yeah… Turns out you can sometimes end up with extra rows despite your best efforts and that SQL isn’t always the best tool for joining data, and no I’m not interested in helping you troubleshoot your 7-page SQL query that’s stacked on top of multiple layers of nested SQL views that’s giving you too many rows. You might even say I’m averse.
crazygringo 2y ago
> In the end I wrote a join tool myself, which can automatically find paths between distant tables in the schema, but it seems ludicrous to have to homebrew something like that. I mean, I think the issue is just that there are lots of possible paths so it can't be automated. If you want to join employees to buildings, is it where their desk is assigned today, or where their desk was assigned two years ago, or where their team is based even though they work remotely, or where they last badged in? Sure you can build a tool to find all potential joins based on foreign keys, but then how do you know which is correct unless you understand what the tables mean? And then if you understand what the tables mean, writing the join out yourself is trivial. > Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables. In my experience, having just one or two ways is for simple/toy projects. Lots of joins in no way means a schema is "fucked up". It probably just means it's correctly modeling actual relationships, correctly normalized.
- Terr_ 2y ago
  Real-world example: Someone wants Job-Application by Country, but that could mean via Applicant's Residence, the Applicant's Nationality, the Requisition Primary location, or one of the Requisition's Satellite Offices, etc. ... And god help you if someday Requisitions need to have Revisions too.
- pjs_ 2y ago
  Yes, this is accurate. You often end up with many solutions and human judgement is sometimes required in the end to pick the right strategy. However, what I have found in practice is that heuristics and hinting can rapidly cut through that complexity. E.g. "always pick the shortest path between tables, and never use this set of tables as intermediate nodes on any path" rules out a ton of options, and usually will leave you with one or two, and usually those are the natural choices. In this way you can use polynomially-many constraints or rules to avoid exponentially-many weird or exceptional routes through the schema. I am optimistic that you can build a system where by default, the autojoin solution is the natural one maybe 90% of the time. There will certainly be exceptions where you have to express the join conditions explicitly. But I think you can dramatically reduce the amount of code required. I would also hazard the suggestion that this might produce productive backpressure on the system. If the autojoiner is struggling to find a good route through the schema, it's possible that the schema is not properly normalized or otherwise messed up.
sgarland 2y ago
> People endlessly want hacks, workarounds, and un-normalized data structures, for a single reason - they don't want to have to think about JOIN. It's not actually for performance reasons, it's not actually for any reason other than it's easier to imagine a big table with lots of columns. That's because, inexplicably, devs by and large don't know SQL, and don't want to learn it. It's an absurdly simple language that should take a competent person a day to get a baseline level of knowledge, and perhaps a week to be extremely comfortable with. As an aside, something you can do is create views (non-materialized) for whatever queries are desired. The counter-arguments to this are that is slows development velocity, but then, so does devs who don't know how to do joins.
- rtpg 2y ago
  it's "absurdly simple", but then you get presented with a bunch of weird things that look like abstraction ceilings like "oh you can't refer to the select clause alias you made in the filter because despite that showing up first lexicographically the ordering is different" and "oh you don't have to refer to the table name except when you do because of ambiguity issues". I think there's a beautiful space for some SQL-like language that just operates a bit more like a general-purpose language in a more regular fashion. Bonus points for ones where you don't query tables but point at indexes or table scans and the like (resolving the "programmer writes query that is super non-performant because they assume an index is present when it's not"). I think it's still super straightforward to sit down and learn it, but it's really unfortunate that we spend a bunch of time in school learning data structures and then SQL tries really hard to hide all that, making it pretty opaque despite people intuitively understanding B-Trees or indexes.
  - iTokio 2y ago
    SQL separates query definition from implementation because there is a planning phase between them that can be sometimes quite complex. To choose the best path to retrieve data, you have to know what are the possible paths (using the underlying data structures, indexes but also different algorithms to filter, join…), but you should also know some data metrics to evaluate if some shortcuts are worth it (a seq scan can be the best choice with a small table..). And the thing that will trip most humans, is that you need to reevaluate the plan if the underlying assumptions change (data distribution has become something that you never expected). Note that the planner is also NOT always right, it heavily relies on heuristics and data metrics that can be skewed or not up to date. Some databases allow the use of hints to choose an index or a specific path.
arkh 2y ago
> Unless your schema is really fucked up So most enterprise schema who have outlived multiple applications. Usually due to time constraint, lack of database administrators and complex business needs. Excel is the king of databases for a reason.
cess11 2y ago
That join you can have your ORM solve for you with some annotations or whatever implying eager loading and so on. But that's only the trivial case, often you want something more complicated, where the order of clauses or what keywords you pick and how you want the results sorted will affect performance in your specific schema on your specific database engine. RDBMS management and querying is a rather deep and experience demanding subject once you step outside the trivial cases. You could put more of it in application code, but it will be a lot of chatter and probably worse outcomes in terms of performance than having people on the team that are really good with databases.
srcreigh 2y ago
You should learn about some basic database internals. In particular the data structure for indexes, the difference between primary and secondary indexes, join algorithms, page size being 8KiB, etc. That knowledge gets you to a place to understand that most joins are very slow due to each DB page containing only 1% useful information for the query algorithm. It will help you see reasons why ppl do things like put an array column in, or use wide tables, etc.
- pjs_ 2y ago
  We use the tool that I wrote on a database with tens of millions of rows. Because the keys are properly indexed, we rarely have performance issues associated with joins. Joins use the index, the Postgres planner is really good, and we can join across heaps of tables with great performance. We have database performance issues for other reasons (loading of redundant information, too many queries) but not really because of joins.
paulmd 2y ago
> Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables. Like say I have apples in boxes in houses. I have an apple table, a box table, and a house table. apples have a box_id, boxes have a house_id. Now I want to find all the apples in a house. Do two joins, cool. But literally everyone seems to write this by hand, and it means that crud apps end up with thousands of nearly identical queries which are mostly just boringly spelling out the chain of joins that needs to be applied. You are primarily describing natural join here. It won’t read your mind and join your tables automatically, but it automatically uses shared columns as join keys and removes the duplicate keys from the result. The problem with any “auto” solution is going to be things like modified dates, which may exist in multiple tables but aren’t shared. Even more magic is natural semi join and natural anti join.
agent281 2y ago
Some databases have the concept of a natural join. It joins two tables on common columns. Not quite what I would want. I would prefer something like a key join that uses foreign key relationships. I don't know if any database has that though. (If anybody does, please let me know.) Oracle: https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturaljoin.html https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturalj... MySQL: https://dev.mysql.com/doc/refman/8.4/en/join.html https://dev.mysql.com/doc/refman/8.4/en/join.html
- pjs_ 2y ago
  Natural join is not jargon that I had heard before but unfortunately it does not refer to what I am talking about - I'm talking about something where the computer automatically figures out how to join across more than two tables.
yunolearn 2y ago
It's not just databases, it's everything: git, HTTP, even programming language features. The industry collectively decided you don't have to actually _know_ anything about anything anymore. And people wonder why software is so broken...
Taikonerd 2y ago
> crud apps end up with thousands of nearly identical queries which are mostly just boringly spelling out the chain of joins that needs to be applied. I think EdgeDB [0] is on the right track here. It's a database built on Postgres, but with a different query language. So instead of manually fiddling with JOIN statements, traversing linked tables just looks like dot notation: select BlogPost.author.email; # get all author emails [0] https://www.edgedb.com/ https://www.edgedb.com/
- Izkata 2y ago
  Django is a python web framework over 15 years old that's also very similar to what GP wants. For example their apple->box->house query would be something like: Apple.objects.get(box__house__owner = 'pjs_') Still have to specify "box", but the actual foreign key relationships are defined on the models so you don't do the full JOIN statements anywhere.
mrgoldenbrown 2y ago
Here's one example of a common complication: Let's say your apple example is for an apple trading app. Each house is related to 2 boxes. One is available_for_trade_box_id and another is save_for_eating_box_id. How does your auto join tool know which to use?

by pjs_ 2y ago

rawgabbit 2y ago

Most databases have the concept of foreign keys. You declare how the tables relate to each other. You can then write a script that queries this metadata to write the join for you. I did this sort of thing over twenty years ago.

Spivak 2y ago
Most ORMs have this feature as well and sorry to say you will hit edge cases where the join is ambiguous and have to manually specify it pretty fast.
- KronisLV 2y ago
  > where the join is ambiguous Join table that maps to an entity in the middle. You can even have multiple columns that have foreign keys against various tables, like some_table_id, other_table_id, another_table_id with only the needed ones being filled out. And in practice, this will be way more manageable than the dynamic mess of the OTLT pattern (table_name and table_id): https://www.red-gate.com/simple-talk/blogs/when-the-fever-is-over-and-ones-work-is-done/ https://www.red-gate.com/simple-talk/blogs/when-the-fever-is... It's not like you have to particularly care about the fact that most of those columns will be empty in practice, as opposed to making your database hard to query or throwing constraints aside altogether.
  - Terr_ 2y ago
    > Join table that maps to an entity in the middle. I'm not not sure what you mean. Are you saying that instead of FK/PK relations like: user.residence_country_id = country.id plus user.citizenship_country_id = country.id You would reify every edge like: user.residence_link_id = link_user_residence.user_id link_user_residence.country_id = country.id user.citizenship_link_id = link_user_citizenship.user_id link_user_citizenship.country_id = country.id Even then, the reverse "I have a country gimme a user" request is ambiguous.
    - Izkata 2y ago
      GP misunderstood what an "ambiguous join" is. Rather than a join that returns multiple results, they're more like when you have relationships like this [0]: user.residence1 -> country user.residence2 -> country user.join(country) You have to specify somewhere which foreign key to follow, it can't be figured out automatically. GP's suggestion doesn't work because there's no way to automatically determine which one to use. I'm only really familiar with Django, and it handles this type of thing by making you specify the "residence1" name instead of the table name "country". [0] To refer back to the top of this comment chain, that user is complaining they can't do something like also have a "country->continent" relationship and do "user.join(continent)" and have the system figure out the two joins needed.

big_whack 2y ago

It's not really a problem of there being combinatorially many ways to join table A to table B, but rather that unless the join is fully-specified those ways will mostly produce different results. Your tool would need to sniff out these ambiguous cases and either fail or prompt the user to specify what they mean. In either case the user isn't saved from understanding joins.

halfcat 2y ago
> ”but rather that unless the join is fully-specified those ways will mostly produce different results.” As a SQL non-expert, I think this is why we are averse to joins, because it’s easy to end up with more rows in the result than you intended, and it’s not always clear how to verify you haven’t ended up in this scenario.
- big_whack 2y ago
  Sorry, but someone who is averse to joins is not a non-expert in SQL, they are a total novice. The answer is like any other programming language. You simply must learn the language fundamentals in order to use it.
  - halfcat 2y ago
    Not sorry, I’ll stick with SQL non-expert as I’ve only worked with databases for a few decades and sometimes run into people who know more. Working with a database you built or can control is kind of a simplistic example. In my experience this most often arises when it’s someone else’s database or API you’re interacting with and do not control. An upstream ERP system doesn’t have unique keys, or it changes the name of a column, or an accounting person adds a custom field, or the accounting system does claim to have unique identifiers but gets upgraded and changes the unique row identifiers that were never supposed to change, or a user deletes a record and recreates the same record with the same name, which now has a different ID so the data in your reporting database no longer has the correct foreign keys, and some of the data has to be cross-referenced from the ERP system with a CRM that only has a flaky API and the only way to get the data is by pulling a CSV report file from an email, which doesn’t have the same field names to reliably correlate the data with the ERP, and worse the CRM makes these user-editable so one of your 200 sales people decides to use their own naming scheme or makes a typo and we have 10 different ways of spelling “New York”, “new york”, “NY”, “newyork2”, “now york”, and yeah… Turns out you can sometimes end up with extra rows despite your best efforts and that SQL isn’t always the best tool for joining data, and no I’m not interested in helping you troubleshoot your 7-page SQL query that’s stacked on top of multiple layers of nested SQL views that’s giving you too many rows. You might even say I’m averse.

crazygringo 2y ago

> In the end I wrote a join tool myself, which can automatically find paths between distant tables in the schema, but it seems ludicrous to have to homebrew something like that. I mean, I think the issue is just that there are lots of possible paths so it can't be automated. If you want to join employees to buildings, is it where their desk is assigned today, or where their desk was assigned two years ago, or where their team is based even though they work remotely, or where they last badged in? Sure you can build a tool to find all potential joins based on foreign keys, but then how do you know which is correct unless you understand what the tables mean? And then if you understand what the tables mean, writing the join out yourself is trivial. > Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables. In my experience, having just one or two ways is for simple/toy projects. Lots of joins in no way means a schema is "fucked up". It probably just means it's correctly modeling actual relationships, correctly normalized.

Terr_ 2y ago
Real-world example: Someone wants Job-Application by Country, but that could mean via Applicant's Residence, the Applicant's Nationality, the Requisition Primary location, or one of the Requisition's Satellite Offices, etc. ... And god help you if someday Requisitions need to have Revisions too.
pjs_ 2y ago
Yes, this is accurate. You often end up with many solutions and human judgement is sometimes required in the end to pick the right strategy. However, what I have found in practice is that heuristics and hinting can rapidly cut through that complexity. E.g. "always pick the shortest path between tables, and never use this set of tables as intermediate nodes on any path" rules out a ton of options, and usually will leave you with one or two, and usually those are the natural choices. In this way you can use polynomially-many constraints or rules to avoid exponentially-many weird or exceptional routes through the schema. I am optimistic that you can build a system where by default, the autojoin solution is the natural one maybe 90% of the time. There will certainly be exceptions where you have to express the join conditions explicitly. But I think you can dramatically reduce the amount of code required. I would also hazard the suggestion that this might produce productive backpressure on the system. If the autojoiner is struggling to find a good route through the schema, it's possible that the schema is not properly normalized or otherwise messed up.

sgarland 2y ago

> People endlessly want hacks, workarounds, and un-normalized data structures, for a single reason - they don't want to have to think about JOIN. It's not actually for performance reasons, it's not actually for any reason other than it's easier to imagine a big table with lots of columns. That's because, inexplicably, devs by and large don't know SQL, and don't want to learn it. It's an absurdly simple language that should take a competent person a day to get a baseline level of knowledge, and perhaps a week to be extremely comfortable with. As an aside, something you can do is create views (non-materialized) for whatever queries are desired. The counter-arguments to this are that is slows development velocity, but then, so does devs who don't know how to do joins.

rtpg 2y ago
it's "absurdly simple", but then you get presented with a bunch of weird things that look like abstraction ceilings like "oh you can't refer to the select clause alias you made in the filter because despite that showing up first lexicographically the ordering is different" and "oh you don't have to refer to the table name except when you do because of ambiguity issues". I think there's a beautiful space for some SQL-like language that just operates a bit more like a general-purpose language in a more regular fashion. Bonus points for ones where you don't query tables but point at indexes or table scans and the like (resolving the "programmer writes query that is super non-performant because they assume an index is present when it's not"). I think it's still super straightforward to sit down and learn it, but it's really unfortunate that we spend a bunch of time in school learning data structures and then SQL tries really hard to hide all that, making it pretty opaque despite people intuitively understanding B-Trees or indexes.
- iTokio 2y ago
  SQL separates query definition from implementation because there is a planning phase between them that can be sometimes quite complex. To choose the best path to retrieve data, you have to know what are the possible paths (using the underlying data structures, indexes but also different algorithms to filter, join…), but you should also know some data metrics to evaluate if some shortcuts are worth it (a seq scan can be the best choice with a small table..). And the thing that will trip most humans, is that you need to reevaluate the plan if the underlying assumptions change (data distribution has become something that you never expected). Note that the planner is also NOT always right, it heavily relies on heuristics and data metrics that can be skewed or not up to date. Some databases allow the use of hints to choose an index or a specific path.

arkh 2y ago

> Unless your schema is really fucked up So most enterprise schema who have outlived multiple applications. Usually due to time constraint, lack of database administrators and complex business needs. Excel is the king of databases for a reason.

cess11 2y ago

That join you can have your ORM solve for you with some annotations or whatever implying eager loading and so on. But that's only the trivial case, often you want something more complicated, where the order of clauses or what keywords you pick and how you want the results sorted will affect performance in your specific schema on your specific database engine. RDBMS management and querying is a rather deep and experience demanding subject once you step outside the trivial cases. You could put more of it in application code, but it will be a lot of chatter and probably worse outcomes in terms of performance than having people on the team that are really good with databases.

srcreigh 2y ago

You should learn about some basic database internals. In particular the data structure for indexes, the difference between primary and secondary indexes, join algorithms, page size being 8KiB, etc. That knowledge gets you to a place to understand that most joins are very slow due to each DB page containing only 1% useful information for the query algorithm. It will help you see reasons why ppl do things like put an array column in, or use wide tables, etc.

pjs_ 2y ago
We use the tool that I wrote on a database with tens of millions of rows. Because the keys are properly indexed, we rarely have performance issues associated with joins. Joins use the index, the Postgres planner is really good, and we can join across heaps of tables with great performance. We have database performance issues for other reasons (loading of redundant information, too many queries) but not really because of joins.

paulmd 2y ago

> Unless your schema is really fucked up, there should only be one or two actually sensible ways to join across multiple tables. Like say I have apples in boxes in houses. I have an apple table, a box table, and a house table. apples have a box_id, boxes have a house_id. Now I want to find all the apples in a house. Do two joins, cool. But literally everyone seems to write this by hand, and it means that crud apps end up with thousands of nearly identical queries which are mostly just boringly spelling out the chain of joins that needs to be applied. You are primarily describing natural join here. It won’t read your mind and join your tables automatically, but it automatically uses shared columns as join keys and removes the duplicate keys from the result. The problem with any “auto” solution is going to be things like modified dates, which may exist in multiple tables but aren’t shared. Even more magic is natural semi join and natural anti join.

agent281 2y ago

Some databases have the concept of a natural join. It joins two tables on common columns. Not quite what I would want. I would prefer something like a key join that uses foreign key relationships. I don't know if any database has that though. (If anybody does, please let me know.) Oracle: https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturaljoin.html https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturalj... MySQL: https://dev.mysql.com/doc/refman/8.4/en/join.html https://dev.mysql.com/doc/refman/8.4/en/join.html

pjs_ 2y ago
Natural join is not jargon that I had heard before but unfortunately it does not refer to what I am talking about - I'm talking about something where the computer automatically figures out how to join across more than two tables.

yunolearn 2y ago

It's not just databases, it's everything: git, HTTP, even programming language features. The industry collectively decided you don't have to actually _know_ anything about anything anymore. And people wonder why software is so broken...

Taikonerd 2y ago

> crud apps end up with thousands of nearly identical queries which are mostly just boringly spelling out the chain of joins that needs to be applied. I think EdgeDB [0] is on the right track here. It's a database built on Postgres, but with a different query language. So instead of manually fiddling with JOIN statements, traversing linked tables just looks like dot notation: select BlogPost.author.email; # get all author emails [0] https://www.edgedb.com/ https://www.edgedb.com/

Izkata 2y ago
Django is a python web framework over 15 years old that's also very similar to what GP wants. For example their apple->box->house query would be something like: Apple.objects.get(box__house__owner = 'pjs_') Still have to specify "box", but the actual foreign key relationships are defined on the models so you don't do the full JOIN statements anywhere.

mrgoldenbrown 2y ago

Here's one example of a common complication: Let's say your apple example is for an apple trading app. Each house is related to 2 boxes. One is available_for_trade_box_id and another is save_for_eating_box_id. How does your auto join tool know which to use?