3 ms·

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 writ

• 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.
        • rawgabbit 2y ago
          Putting on my dogmatic 3NF purist hat. Instead of denormalized user.residence1 -> country.id user.residence2 -> country.id I would create a many to many bridge table. To accommodate multiple residences for a given user. bridge.userid-> user.id bridge.countryid-> country.id
          • Izkata 2y ago
            Oh definitely, I just couldn't think of a better example off the top of my head. But also not really relevant: You could also have another table and the relationships be A -> B -> D and A -> C -> D, with the desired join between A and D. Remember this whole thread is about the system figuring out the JOIN criteria automatically.
• pjs_ 2y ago
  Yep, that's exactly what I did - using the ORM schema definition rather than the schema directly, but same difference