undefined | Better HN

0 pointsrenke16y ago0 comments

That's hard to say, because I am not sure what I really want.

…but let's say I have this result (from an arbitrary query).

  | user.id | user.name | post.id | post.title |
  +---------+-----------+---------+------------+
  | 1       | renke1    | 1       | first      |
  | 2       | alde      | 2       | second     |
  | 2       | alde      | 3       | third      |

Now I would like to tell the library: hey, an user can have many posts (1:n), please map this to nested objects.

Of course I don't want to write `SELECT user.id, user.name … FROM …` but just `SELECT * FROM …` (because a table may have a dozen of columns and I don't want to spell out every single one). So the query might have to be rewritten on-the-fly to make the correct projection (otherwise it would be hard to know to which object a value belongs).

I am not sure if that's something your library should do though.

And thinking even more about it, I think this approach wouldn't really work for views (and probably other things) where it's not really clear from which tables the data actually comes from (at least not by only looking at the query).

I guess what I really want is library that takes my SQL query, reads my mind and gives me back some nested objects… and let's not talk about inserts…

0 comments

3 comments · 1 top-level

gigatexal6y ago· 2 in thread

but select * is an anti-pattern. SQL queries should only return the columns that you need not that you might need -- usually. Lazy loading and sessions etc., like SQLAlchemy does to get around the N+1 query problem be damned.

renke1OP6y ago

I didn't mean the `SELECT STAR` in a literal sense, but more like, please select only the stuff we need. But indeed with PgTyped, as far as I understand it, it wouldn't work because it creates interface from SQL queries.

To achieve what I want you need to do it like all the other ORMs where you have some kind of description of your model and how it maps to tables and columns. Unlike the usual ORM I want to write SQL queries which are then rewritten in an intelligent manner.

So something like query(`SELECT STAR FROM user LEFT JOIN post …`, UserWithPostsModel). Since the library would know the target model, it could rewrite the `SELECT STAR` to something that only asks for the data it needs.

In other words I want to execute arbitrary queries that a mapped into ad-hoc models (unlike typical ORMs where the model usually maps directly to tables).

  STAR = *

gigatexal6y ago

Ahh I understand you now.

j / k navigate · click thread line to collapse

| user.id | user.name | post.id | post.title | +---------+-----------+---------+------------+ | 1 | renke1 | 1 | first | | 2 | alde | 2 | second | | 2 | alde | 3 | third |