4 ms·

With a good library you could do just that, by having the functions return only queries and then expand them to the actual values (by interacting with the DB) a

by _flux 1y ago

With a good library you could do just that, by having the functions return only queries and then expand them to the actual values (by interacting with the DB) after applying the filtering to it?

soulofmischief 1y ago
So would you then have to do `getActualUsers(db.getUsers())` or `query(db.getUsers())`? Still smells like in such a case the developer avoids the complications of abstraction or OOP by making the user deal with it. That's bad API design due to putting ideology before practicality or ergonomics.
- bulatb 1y ago
  You would have an API that makes the query shape, the query instance with specific values, and the execution of the query three different things. My examples here are SQLAlchemy in Python, but LINQ in C# and a bunch of others use the same idea. The query shape would be: active_users = Query(User).filter(active=True) That gives you an expression object which only encodes an intent. Then you have the option to make basic templates you can build from: def active_users_except(exclude): return active_users.filter(User.id.not_in(exclude) ...where `exclude` is any set-valued expression. Then at execution time, the objects representing query expressions are rendered into queries and sent to the database: exclude_criterion = rude_users() # A subquery expression polite_active_users = load_records( active_users_except(exclude_criterion) ) With SQLAlchemy, I'll usually make simple dataclasses for the query shapes because "get_something" or "select_something" names are confusing when they're not really for actions. @dataclass class ActiveUsers(QueryTemplate): active_if: Expression = User.active == true() @classmethod excluding(cls, bad_set): return cls( and_( User.active == true(), User.id.not_in(bad_set) ) ) @property def query(self): return Query(User).filter(self.active_if) load_records( ActiveUsers.excluding(select_alice | select_bob).query )
  - t_mahmood 1y ago
    As in Django querysets. But starts to get messy with complex queries.
    - bulatb 1y ago
      It can. SQLAlchemy has good support for types since 2.0, which helps a lot.
  - soulofmischief 1y ago
    This is a better story because it has consistent semantics and a specific query structure. The db.getUsers() approach is not part of a well-thought-out query structure.
- shortrounddev2 1y ago
  In linq (C#) IEnumerable<User> getExpiredUsers(DbSet<User> users) => users.Where(u => u.ExpiresAt < DateTime.UtcNow); Such simple logical expressions (called expression trees) get converted to SQL queries
  - ryanrasti 1y ago
    The is exactly the way forward: encapsulation (the function), type safety, and dynamic/lazy query construction. I'm building a new project, Typegres, on this same philosophy for the modern web stack (TypeScript/PostgreSQL). We can take your example a step further and blur the lines between database columns and computed business logic, building the "functional core" right in the model: // This method compiles directly to a SQL expression class User extends db.User { isExpired() { return this.expiresAt.lt(now()); } } const expired = await User.where((u) => u.isExpired()); Here's the playground if that looks interesting: https://typegres.com/play/ https://typegres.com/play/
  - quails8mydog 1y ago
    And anyone who calls that method may find themselves dealing with the implementation details of Entity Framework and whatever db provider you're using because it's a leaky abstraction.

by _flux 1y ago

With a good library you could do just that, by having the functions return only queries and then expand them to the actual values (by interacting with the DB) after applying the filtering to it?

soulofmischief 1y ago

So would you then have to do `getActualUsers(db.getUsers())` or `query(db.getUsers())`? Still smells like in such a case the developer avoids the complications of abstraction or OOP by making the user deal with it. That's bad API design due to putting ideology before practicality or ergonomics.

bulatb 1y ago
You would have an API that makes the query shape, the query instance with specific values, and the execution of the query three different things. My examples here are SQLAlchemy in Python, but LINQ in C# and a bunch of others use the same idea. The query shape would be: active_users = Query(User).filter(active=True) That gives you an expression object which only encodes an intent. Then you have the option to make basic templates you can build from: def active_users_except(exclude): return active_users.filter(User.id.not_in(exclude) ...where `exclude` is any set-valued expression. Then at execution time, the objects representing query expressions are rendered into queries and sent to the database: exclude_criterion = rude_users() # A subquery expression polite_active_users = load_records( active_users_except(exclude_criterion) ) With SQLAlchemy, I'll usually make simple dataclasses for the query shapes because "get_something" or "select_something" names are confusing when they're not really for actions. @dataclass class ActiveUsers(QueryTemplate): active_if: Expression = User.active == true() @classmethod excluding(cls, bad_set): return cls( and_( User.active == true(), User.id.not_in(bad_set) ) ) @property def query(self): return Query(User).filter(self.active_if) load_records( ActiveUsers.excluding(select_alice | select_bob).query )
- t_mahmood 1y ago
  As in Django querysets. But starts to get messy with complex queries.
  - bulatb 1y ago
    It can. SQLAlchemy has good support for types since 2.0, which helps a lot.
- soulofmischief 1y ago
  This is a better story because it has consistent semantics and a specific query structure. The db.getUsers() approach is not part of a well-thought-out query structure.
shortrounddev2 1y ago
In linq (C#) IEnumerable<User> getExpiredUsers(DbSet<User> users) => users.Where(u => u.ExpiresAt < DateTime.UtcNow); Such simple logical expressions (called expression trees) get converted to SQL queries
- ryanrasti 1y ago
  The is exactly the way forward: encapsulation (the function), type safety, and dynamic/lazy query construction. I'm building a new project, Typegres, on this same philosophy for the modern web stack (TypeScript/PostgreSQL). We can take your example a step further and blur the lines between database columns and computed business logic, building the "functional core" right in the model: // This method compiles directly to a SQL expression class User extends db.User { isExpired() { return this.expiresAt.lt(now()); } } const expired = await User.where((u) => u.isExpired()); Here's the playground if that looks interesting: https://typegres.com/play/ https://typegres.com/play/
- quails8mydog 1y ago
  And anyone who calls that method may find themselves dealing with the implementation details of Entity Framework and whatever db provider you're using because it's a leaky abstraction.