1

I've got the following data structure:

[
 {
   "postID": 1, 
   "images": [
      {"imageID": 1, "pos": 1, "uploaded": "2022-01-01", "tags": []}, 
      {"imageID": 2, "pos": 2, "uploaded": "2022-01-01", "tags": []}
    ]
 },
 {
   "postID": 2, 
   "images": [
      {"imageID": 3, "pos": 1, "uploaded": "2022-01-01", "tags": []}, 
      {"imageID": 4, "pos": 2, "uploaded": "2022-01-01", "tags": []}
    ]
 }
]

How can I ORDER BY the most recent (or oldest) dates for each post, where the date is selected to be the most recent/oldest from each of the images? Note that I still want to maintain the order of the images according to the "pos" column.

This is my query that generates this data:

WITH image_tags AS (
    SELECT images."post id", 
           json_build_object (
               'imageID', images."image id",
               'uploaded', images."uploaded",
               'tags', json_agg("tag map".tag) ) AS image
    FROM images
    JOIN "tag map" ON images."image id" = "tag map"."image id"
    GROUP BY images."post id", images."image id"
    ORDER BY images."pos" DESC
)
SELECT posts."post id" AS "postID", 
       json_agg(image_tags.image) AS images
FROM posts
JOIN image_tags ON posts."post id" = image_tags."post id"
GROUP BY posts."post id"
--ORDER BY ?

Possible alternatives: Instead I move uploaded date to the post table, but these means that I won't be able to find the individual upload dates for each of the images. So that is a last resort only if this isn't possible to do.

Improve this question
2
  • My image table already has a foreign key to posts."post id". I just exclude it in the JSON output because it would be redundant. Commented Apr 7, 2022 at 16:12
  • Well I misread that entirely. Commented Apr 7, 2022 at 16:15

1 Answer 1

Reset to default
0

You have all the necessary information in your images table. (Since you did not show the definition of that table, what follows is assuming a couple of things.) A window function should do the trick:

WITH image_tags AS (
    SELECT images."post id", 
           json_build_object (
               'imageID', images."image id",
               'uploaded', images.uploaded,
               'tags', json_agg("tag map".tag) ) AS image,
           first_value(images.uploaded) OVER (
               PARTITION BY images."post id" ORDER BY images.uploaded DESC
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS newest,
    FROM images
    JOIN "tag map" ON images."image id" = "tag map"."image id"
    GROUP BY images."post id", images."image id"
    ORDER BY images."pos" DESC
)
SELECT posts."post id" AS "postID", 
       json_agg(image_tags.image) AS images
FROM posts
JOIN image_tags USING ("post id")
GROUP BY posts."post id"
ORDER BY image_tags.newest -- ASC by default, use DESC for reverse order

The window function takes all the rows in the images table that have the same "post id" and makes them available for analysis. You can then find the first and the last row of the window frame and store the value in new columns in the image_tags row source. After that the ordering in the main query is straightforward.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I tried running this exact query (no changes) and I am getting "error: syntax error at or near "GROUP"". Would you mind checking over that syntax for the Window?
It appears that you can remove the entire GROUP BY clause from the CTE because I do not see any aggregate function that require it
The GROUP BY is needed if I remove it I get the error: error: column "images.post id" must appear in the GROUP BY clause or be used in an aggregate function. I can't seem to use the Window function wherever I put it postgres complains of a syntax error.
Edit: I'm using PostgreSQL 14
Ok I've gotten this to run by replacing the whole "w" with "(PARTITION BY images."post id" ORDER BY images."uploaded" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)" and removing WINDOW AS, because it doesn't seem to like that. However, I can't seem to get it to work. It is always returning results sorted by lowest post id to highest post id (same as before).
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.