@JackDouglas Yeah, I wonder why they advised that it shouldn't be used.

Thus part also makes no sense to me:

> Thus, the dual table is a way to perform operations against what amounts to be an empty but not null table.

"empty but not null" table?

@JackDouglas I put your connect by level example in one of the answers that had a similar one with recursive CTE.

@ypercubeᵀᴹ I'm not sure Brian is going to get the message — he's not been around since 2015

but thanks for trying

good afternoon?

@McNets afternoon :)

Hi @JackDouglas

@JackDouglas He is active at rpg

OK. "active" is not a proper description ;)

-13 is surely not negative enough, but it is on SO so hey :S

.

How is everyone?

@JackDouglas but it is the accepted answer and has a 50 bounty from the OP!

Hi @EvanCarroll, have you seen my msg?

which one?

maybe not

I use the phone app and I wonder if it eats messages sometimes

linkedin

ohh

let me check it

i almost never use it

me neither

yea, just checked it out. that's cool you've found it at a few libraries. We just need to get someone to scan it now. I'm pretty sure that while there are clearly very few copies of it remaining, it seems likely that it's not extinct -- yet.

yes, still a few ones alive

Hi folks

Any rules to this room?

Be gentle.

cool :D

I have a question about what the "done thing" is for collecting rows of related tables

so, let's say we have a table of galleries. A gallery can have a name and id. We have a table of images, each image having a name, an id, and the id of a gallery that it belongs to

if I want to get all galleries and images and pack them up into JSON, what is the "correct" way to do this and what is this called?

there are a few approaches

1) join galleries with images on gallery.id = image.gallery_id. This is good because I'm not writing dynamic SQL but bad because I get a duplicate gallery "half-row" for each image

2) do a query for selecting all galleries, then generate a query like select * from images where gallery_id in (gallery[0].id, gallery[1].id, ..., gallery[n].id)

3) do a query for each gallery row

4) do a query for all galleries, then do a join on all galleries with just their id

eh I think I just needed to rubber duck

I'll go with 4

thanks :D

5) do a join - like (1) - and group by gallery.id. Aggregate into text or json. It depends on your DBMS and what support it has for json type.

@Ell What does "I get a duplicate gallery 'half-row' for each image" mean?

On query per gallery sounds like a performance problem waiting to happen.

@ErikE I suppose it mean that the result set has the data from gallery duplicated.

@ypercubeᵀᴹ I see.

gallery.id | gallery.name | image.id | image.name | image.gallery_id
     0     | "holiday"    |     0    | "beach"    | 0
     0     | "holiday"    |     3    | "pool"     | 0
     0     | "holiday"    |     2    | "hotel"    | 0

@ErikE Not sure how much that matters when one wants a result set of a whole table. Do DBMS (eg SQL Server) do compression when sending results?

^the gallery "half" of the results are all the same

I figured the "correct" approach of going from RDBMS to OOP style of data would be solved by now, but I don't really know what to google

CREATE TABLE #GalleryIds (GalleryId int NOT NULL PRIMARY KEY CLUSTERED);
SELECT g.* OUTPUT g.GalleryId INTO #GalleryIds FROM dbo.Gallery g WHERE YourCondition = true;
SELECT i.*
FROM
   dbo.Image i
   INNER JOIN #GalleryIds g
      ON i.GalleryId = g.GalleryId;

There. You get two rowsets, no dynamic SQL, no duplication of values, no sending gallery IDs back to the database, and clean shapes of rowsets.

Your Json can be similar and the web site can stick the images into an object with the Gallery ID as key indexing into an array of images.

let images = [{ galleryId: 1, name: 'dog', url: 'http://dog.com/fido.png'}, { galleryId: 1, name: 'cat', url: 'http://dog.com/hated.png' }, { galleryId: 2, name: 'polka', url: 'http:/dog.com/dancing.png' }];
let imagesByGallery = images.reduce(
   (obj, image) => {
      if (!obj.hasOwnProperty[image.galleryId]) {
         obj[image.galleryId] = [];
      }
      obj[image.galleryId].push(image);
      return obj;
   },
   {}
);

Oops, hasOwnProperty should have parentheses, not square brackets. Sorry: if (!obj.hasOwnProperty(image.galleryId)) {. I wonder if a moderator can ftfm?

it's okay, I'm doing python anyway, it's the SQL I'm having trouble with

That is javascript

But in any case, it returns this:

{
   "1": [
      {
         "galleryId": 1,
         "name": "dog",
         "url": "http://dog.com/fido.png"
      },
      {
         "galleryId": 1,
         "name": "cat",
         "url": "http://dog.com/hated.png"
      }
   ],
   "2": [
      {
         "galleryId": 2,
         "name": "polka",
         "url": "http:/dog.com/dancing.png"
      }
   ]
}

Once you have that dictionary you can do all the lookups into the dictionary that you like in algorithmic time O(log(n))

@ErikE thanks for the help! I'll read over the SQL to understand it :)

does the # mean a temporary table?

In SQL Server, yes that's a temporary table you create on the fly.

What's your DBMS?

Some people do simply tolerate the rowset with all the duplicated information in it. It's a valid strategy if performance is good enough. It depends a bit on the code you're using and how hard it is to change the shape of things and process the results.

This code uses the "just join it all and work it out in the back end" strategy: github.com/jonwagner/Insight.Database (C#, but could give you ideas).

I also found this: ponyorm.com

That was the product of searching for "fastest lightweight orm"

I also saw some potentially interesting links with a search for "strategies for object relational querying with parent child relationship"

I can't find the article I read a while back about some of the different strategies.

I can tell you that for very large and wide rowsets, the "join it and tolerate duplication" strategy may not work very well...

Especially because the moment that you add a third table, a grandchild if you will, the number of query rows starts exploding.

10 galleries with 100 images, okay 1,000 rows. If each of the 100 images has 25 metadata properties, you get 25,000 rows. Doing them sequentially would just be 1,110 rows, so you can see how it starts to grow even with somewhat reasonable numbers.

Tag me if you have more questions or thoughts.

@ErikE it's postgresql

@Ell Postgres has json and jsonb type, so you do the conversion in the database.

postgresql.org/docs/current/static/functions-json.html

@ypercubeᵀᴹ It seems reasonable to do it in the database if that's necessary and useful, but I don't see that the capability necessarily demands using it.

JSON is usually a transport concern between a back end and a client. Getting it directly out of the database seems unusual unless the back end code will function as a transparent pass-through.

In my experience, forcing database schemas and DTOs (data-transfer objects) on other edges of the system to align in their names, properties, and parts, ends up being rigid and ultimately, fragile design.

Put it this way: if you need to cosmetically change a UI element or even change the way that the same information is being represented across the wire between the back and and a client, you should not be touching your database querying to do that. That is an improper separation of concerns.

If your use case is that in fact the transport layer between the database and the consumer (whether that is the back end or, is logically the client, where the back end functions as a mere-pass-through) then the JSON from the database makes perfect sense.

@ErikE I meant "you could do ..."

Haha, me and my soap box. :)

Having a proper "seam" between the database's schema and the object mapping in the code, then another "seam" between the back end and the UI, give your system much more flexibility and helps it be designed correctly. You get all the benefits of strong typing within the middle layer (and ought to strongly type as MUCH as possible there).

Okay I'm done.

Well, one example of personal, painful experience (not caused by me, but suffered by me). Dates stored in the database as pacific time need to be converted to proper time zones in outbound messages.

General screaming.

The only reasonable way to handle that was to convert the non-time-zone-decorated times at query time, into proper objects in the back end (in C#, that's DateTimeOffset or use Noda time or some other library). Then, shifting time zones is a cake walk. Finally, convert back to a text-based time that has no time zone information in it but is the correct time zone for the output.

If you've written your entire infrastructure around the database producing the JSON and suddenly have a requirement to do DST-aware time zone shifting, you might be in a world of hurt.

@ypercubeᵀᴹ well, JSON would be just one use case

I should have made that clear

I guess the general aim is just doing RDBMS -> OOP style of data storage

Without using an ORM as the back end

@ErikE I agree wholeheartedly with this

@ypercubeᵀᴹ Sorry if it seemed I was going off on you. That wasn't my intent. Mostly, I suddenly realized that I have something to say on this topic--like it has finally crystallized in my mind and by saying it out loud it was becoming real to me in a way I'd never known before.

@ErikE No issue. I didn't appear as going off on anyone. I agree with all you said

@ErikE okay I have read this through and tested it out, it seems like exactly what I want :)

I couldn't find a postgresql alternative to OUTPUT though

so currently I'm doing an INSERT followed by a SELECT

it'd be nice to find the equivalent though, but I'm going to bed, thank you very much for the help, I'll probably be back tomorrow with any more info I've found

@Ell It looks like the RETURNING clause is the equivalent.

@Ell postgresql.org/docs/current/static/sql-insert.html

@Ell For what it's worth, I found that by the search "postgre output clause" and clicked on the first link, then clicked "current version".