I'm always torn on how to actually implement this advice though. Sure its faster and in some ways easier to use the built in SQL stuff, especially if you're trying to reduce down to a value like with sum, but how can you deal with that in an ORM-esque way so you still have a type safe representation of that row for manipulating down the way? I can see the desire to have some custom calculated row in the return from sql but then what happens to the representation when you pass it around? I'm not sure how to balance these two things I guess, the pure SQL and the classed OOP type safe current approach.
This is the million dollar question :) Yes as soon as you use an ORM your ability to run raw SQL queries becomes difficult. This depends on you and your team to set a guideline to come around this problem. For that, it is better to talk milliseconds and performance choosing a solution that is fast in execution and relatively easier to write and maintain would be my suggestion. Thanks!
You isolate the code that uses the custom SQL, you document it and test it well and like everything else, you try to have all the code that needs it use that single function that returns the DB value.
Your app can still use the ORM for 99%, but that doesn't mean we shouldn't take advantage of the DB if after measuring we conclude that the tradeoff is worth it.
one way is to compose the orm representation of the data from the raw table and lazy loaded views mapped readonly. In case of JPA this means @SecondaryTable pointing to a View and columns with insertable=false, updatable=false. by this they never appear in any insert or update statement and the additional aggregation logic is cleanly separated from the raw data model. works fine.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
I'm always torn on how to actually implement this advice though. Sure its faster and in some ways easier to use the built in SQL stuff, especially if you're trying to reduce down to a value like with sum, but how can you deal with that in an ORM-esque way so you still have a type safe representation of that row for manipulating down the way? I can see the desire to have some custom calculated row in the return from sql but then what happens to the representation when you pass it around? I'm not sure how to balance these two things I guess, the pure SQL and the classed OOP type safe current approach.
This is the million dollar question :) Yes as soon as you use an ORM your ability to run raw SQL queries becomes difficult. This depends on you and your team to set a guideline to come around this problem. For that, it is better to talk milliseconds and performance choosing a solution that is fast in execution and relatively easier to write and maintain would be my suggestion. Thanks!
The balance is isolation.
You isolate the code that uses the custom SQL, you document it and test it well and like everything else, you try to have all the code that needs it use that single function that returns the DB value.
Your app can still use the ORM for 99%, but that doesn't mean we shouldn't take advantage of the DB if after measuring we conclude that the tradeoff is worth it.
one way is to compose the orm representation of the data from the raw table and lazy loaded views mapped readonly. In case of JPA this means @SecondaryTable pointing to a View and columns with insertable=false, updatable=false. by this they never appear in any insert or update statement and the additional aggregation logic is cleanly separated from the raw data model. works fine.