To clarify this, this is an event where a container is holding multiple media. So if a user uploads 5 media at once, they're all assigned the same container. When fetching, if it so happens that only 3 of those media are able to be rendered before we hit our limit, we'll be hitting the same container in the next fetch.

Due to the nature of how the media are stored in messages, they're all grouped by uploads.container = messages.id, so we will get the 3 from last render and the 2 new ones. For example:

[
 {"type":"video",
  "uploadID":"107022",
   "thumbnailUploadID":"107023"
 },
 {"type":"photo",
  "uploadID":"107025"
 },
 {"type":"photo",
  "uploadID":"107024"
 },
 {"type":"photo",
  "uploadID":"107021"
 },
 {"type":"photo",
  "uploadID":"107020"
 }
]'

So video (107022), photo (107025) and photo (107024) will be rendered the first time, and when we need to fetch more data, we'll get the same 5 media and we'd filter out the IDs we already have and just render photo (107021) and photo (107020).

I needed a type for request here to satisfy flow in my editor, and ThreadFetchMediaRequest is introduced in the next diff and it's fixed there

Would you prefer me to combine the two diffs into one?

In terms of the creation_time here, I thought that ordering by creation_time is ideal so we can render the media from newest --> oldest

Also sure, here are some rows with all three, here are 12 records. The thumbnails will be with type: "photo" and their filename will begin with "thumb".

The types should always come first. I don't need you need to squash the diffs... you can either move the types you need over here, or potentially reorder the diffs if that's possible

Nice!! Now can you LEFT JOIN in the container?

You'll first need to first out what table each of the containers is in, so that you can craft the query. To do that, start by LEFT JOIN-ing the ids table. You won't look at ids in your final query, but right now you just want to see the table_name for each ID.

Once you know the tables you need to LEFT JOIN, craft a query with a LEFT JOIN for each individual table. Eg. if you seem that some containers (maybe all?) are messages, then join them in and select the content column. Now share the results of that query here and we can figure out how to proceed.

• First need to figure out

I know that all containers = some message id, so I can left join on upload.container = messages.id.

Here's my query with LEFT JOIN. I know that all uploads.container corresponds with a messages.id.

The results show that each result with a container has it's content field duplicated across each of the results that have the same container (since my query just gets the entire messages.content field for that specific container)

Sorry, here is the corrected query without the GROUP BY

Awesome!! So it looks like we have all the data we need here, but we'll need to do a JSON_SEARCH / JSON_EXTRACT on the content column to get it.

Maybe we want our ultimate query to be something like this:

1. Query for all messages of multimedia / images type for this thread
2. Left join in uploads table where container matches the message ID
3. Group by discrete media item
   • This will be a little hard since a message can have multiple media, and a media can have multiple uploads (video + thumbnail)
   • I think the approach should be to group by the uploadID of the object in the content array that corresponds to the row. So if the row is a photo / video, we group by its id. But if the row is a video thumbnail, we group by the uploadID that is in the same object as its thumbnailUploadID

This might be a hard SQL query to craft... feel free to grab some time with me tomorrow (maybe 4pm ET?) and we can work on it together.

Here's the query that works from our 1:1:

SELECT content.photo AS uploadID,
  u.secret AS uploadSecret,
  u.type AS uploadType, u.extra AS uploadExtra,
  u.container,
  NULL AS thumbnailID,
  NULL AS thumbnailUploadSecret,
  NULL AS thumbnailUploadType,
  NULL AS thumbnailUploadExtra
FROM messages m
LEFT JOIN JSON_TABLE(
  m.content,
  "$[*]" COLUMNS(photo INT PATH "$")
) content ON 1
LEFT JOIN uploads u ON u.id = content.photo
WHERE m.thread = ${request.threadID} AND m.type = 14
UNION SELECT content.media AS uploadID,
  uv.secret AS uploadSecret,
  uv.type AS uploadType, uv.extra AS uploadExtra,
  uv.container,
  content.thumbnail AS thumbnailID,
  ut.secret AS thumbnailUploadSecret,
  ut.type AS thumbnailUploadType,
  ut.extra AS thumbnailUploadExtra
FROM messages m
LEFT JOIN JSON_TABLE(
  m.content,
  "$[*]" COLUMNS(
    media INT PATH "$.uploadID",
    thumbnail INT PATH "$.thumbnailUploadID"
  )
) content ON 1
LEFT JOIN uploads uv ON uv.id = content.media
LEFT JOIN uploads ut ON ut.id = content.thumbnail
WHERE m.thread = ${request.threadID} AND m.type = 15;

(It's missing LIMIT and ORDER BY, though.)

Oh and you should use messageTypes constants instead of 14 and 15