
Using SQLAlchemy and Postgres functions to produce JSON structures from joins - anewhnaccount2
https://trvrm.github.io/using-sqlalchemy-and-postgres-functions-to-produce-json-tree-structures-from-sql-joins.html
======
munk-a
I'm "eh" on ORMs, so if there are ORMers out there... one of the things I find
hurts SQL and data-model comprehension is the compounded complexity of
relationships. Maybe you have authors and books - but more likely your data-
model is more complex, maybe your business needs group authors by family and
you also track how many copies of books are in libraries. This is still a
pretty simple situation but as things get more complicated the utilization of
non-materialized views can help simplify expression - but they come at the
cost of pushing some of your logic into the DB where normal developers can't
see it.

Have people using SQLAlchemy and other ORMs found it helpful to create fake
views using the ORM system (i.e. pre-define a commonly used join) so that it
can be reused as a simpler black-box unit of code where needed?

~~~
jperras
It's relatively straightforward to map a materialized view to an ORM entity in
SQLAlchemy, if that's what you want to do:

[http://www.jeffwidman.com/blog/847/using-sqlalchemy-to-
creat...](http://www.jeffwidman.com/blog/847/using-sqlalchemy-to-create-and-
manage-postgresql-materialized-views/)

SQLAlchemy's ability to separate out the query API, metadata, data objects,
and combine them all via mappers is very powerful. I've been using SQLA for
many years, and I still regularly find some functionality that I was never
aware of.

~~~
munk-a
I more actually meant something closer to a non-materialized view, just a
query segment definition that gets frequently reused - supporting it in a
materialized form is all good and well but I'm more interested in how
repetitive SQLA tends to come out, if a common set of tables is joined through
in a wide variety of queries is there a way to reduce the number of times you
can spell it out in an easy manner?

------
mullsork
My biggest gripe with joining and aggregating to JSON is JSON itself. The
project I'm working on is dealing with timestamp ranges which of course can't
be serialized to JSON.

Although I'm not working with SQLAlchemy (or Python) I presume it has the
power to serialize a TSRANGE into a native Python range (if it has that) as
well.

I'm curious if anyone has ever found some way around that, I feel like it
isn't possible UNLESS there's some data type than JSON available that I could
aggregate with.

~~~
dragonwriter
> The project I'm working on is dealing with timestamp ranges which of course
> can't be serialized to JSON.

JSON is a low-level serialization format, on which you often need to build
application-specific high-level formats. You can serialize a tsrange into JSON
a number of ways, but your deserializer will.need to be aware of the higher-
level format you create by your decisions on how to serialize unsupported
types as composites of supported types.

~~~
mullsork
Very fair to say that this logic ought to exist on the application level, I
suppose.

