Normalizing Query Cross Join Data Using Namespaces In ColdFusion

My brain always starts with the data and the database, So I cringe when I look at database tables that don't prefix the column names with the table name.
In a SQL select statement:

p.poem_id, p.poem_name, s.share_id, s.share_name

Looks much more clear to me than:

p.id, p.name, s.id, s.name

So what you accomplish with the aliases in your example select statement, I much prefer to see that as part of the table schema so the data is always clearly identified as to which table it belongs to in the database.

For your statement:

And, to be fair, this is partly for aesthetics - outputting entry.poem.name is just objectively nicer looking than outputting entry.poem_name. And, for me, how the code looks is just as important as how the code works.

I agree with this; if you're only going to manipulate that data using the structures then there is consistency and cleanliness. Especially knowing that you can always guess that most of the structs will have a id and name key without having to go back and double check things.

I'm not sure I had much of a point to this comment, I guess I just wanted to give you some engagement to encourage you to keep doing what your doing and let you know I appreciate your sharing of your process with us!

Ben,

You know I'm always preaching the "Let the DB do what it's great at". Rather than doing all this transformation in code, why not get the resulting JSON you want right away from the DB? Most major DBMSs now support full relational to JSON native transformation. For example, in MySQL you can do the following:

SELECT
  COALESCE(
    JSON_ARRAYAGG(
      JSON_OBJECT(
        'poem',  JSON_OBJECT(
          'id',   p.id,
          'name', p.name
        ),
        'share', JSON_OBJECT(
          'id',        s.id,
          'name',      s.name,
          'viewCount', s.viewCount
        )
      )
      ORDER BY p.id, s.id
    ),
    JSON_ARRAY()
  ) AS data
FROM 
  poem AS p
  JOIN share AS s
    ON s.poemID = p.id
WHERE 
  p.userID = :userID;

this will give you the result needed in the format:

[
  { "poem": { "id": 1, "name": "First poem" }, "share": { "id": 101, "name": "Public link", "viewCount": 4 } },
  { "poem": { "id": 1, "name": "First poem" }, "share": { "id": 102, "name": "Family chat", "viewCount": 9 } },
  { "poem": { "id": 2, "name": "Second poem" }, "share": { "id": 203, "name": "Blog demo", "viewCount": 25 } },
  { "poem": { "id": 3, "name": "Third poem"  }, "share": { "id": 304, "name": "Public link", "viewCount": 8 } }
]

It never ceases to amaze me how much our cfml journeys are parallel. It's almost creepy, and since you started blogging I felt that I never needed to mostly, as you already say what I would have posted anyway, from the time you started until now. That said there is much I should have blogged but didn't due to this also. So thanks for saving me time #lol

Was just extending my dal and moving to object-based queries like this in my teamcfml framework dal module. It also helps returning api data in graph-like ways in an rest-api also.

Cheers again Mr Nadel.

IMHO you need both techniques, as quite often manipulating data from different sources (db,clickhouse,social media, apis) to combine into one useful data object for presentation layer also.

That said i'm enjoying sql 2025 with polybase, and the ease that combination brings from ETL by allowing you to query sql and excel/csv files in the same query without having to import into the db first!

So yeah, bringing data from all kinds of places to be useful in queries adds complexity, but my dal takes that all away.

just call tc.dal.getNestedPagePoemShares(2,20);