Show all (my, their, everyone's) uploads


(Tom Newsom) #1

AFAICT, this doesn’t exist yet?

I’d like to see a list or thumbnail gallery of all my uploads.
Actually I’d like to see someone else’s uploads.
Actually, as admin I’d like to see everyone’s uploads.

Use cases:

I attached an image a few weeks ago and it’s relevant to this new discussion. It was a direct cut-n-paste job so I don’t have the original. I can’t remember where I posted it, and I can’t search for an image.

Ditto, but it was user x who posted it in… which thread? when? It was a great image and I want to share it again.

I want to see what sort of files our users are uploading and also make sure that no single user is abusing the upload facility.

I can make mockups if you like, but I think this feature describes itself.


(Régis Hanol) #2

That always helps :wink:


(Tom Newsom) #3

I knew I wouldn’t be able to get away with it :smiley:

In profile (this works for your own and other people’s)

The admin view would be very similar, but with avatars next to each one (and maybe an option to delete uploads?)

And then in the composer


(Tobias Eigen) #4

I like this! I’ve also often wished to be able to see a gallery of all pictures uploaded in a single topic, presented in a grid format with a slide show option.


(Joshua Rosenfeld) #5

This would be a great feature! If the size of the file could be its own column, then one could (especially admins and mods) could sort by size when a cleanup is needed. Additionally, having an option (MediaWiki comes to mind) to see where the image is used would be great, so one does not affect a current thread by deleting an image.

Also, @Tom_Newsom, how would this work for private/restricted categories? If I posted in the Lounge (for example), would a new user see that image on my user profile?


(Tom Newsom) #6

Now we’re getting stuck into the ugly details, which I suspect will make this hard to implement :slightly_smiling: It’s also the point at which my knowledge of Discourse internals dries up so I have no idea how hard/easy that would be.

If all else fails, it would still be very useful to see your own uploads (and for admins to track down the gigantic ones that are taking up all that space on the server etc.)

Just thought of a further complication - if an admin adds an upload to someone else’s post, or in the case of a wiki post with many uploads, who do those uploads “belong” to? Unless the information is captured at the time of uploading, it would be impossible to know…


(Joshua Rosenfeld) #7

Well, considering that all edits are logged, even those of admins, I would think it would be attributed to whoever did the uploading (regardless of the “owner” of the post).


(Tom Newsom) #8

I was reminded by Search for pinned topics:

Maybe a simple version of this feature could be implemented with a has:upload search filter?

Use case: Our members often post pictures, which our “marketing” team would like to send to our flickr/twitter/instagram etc. That team would like to be able to see a condensed list of recent uploads.


(Jeff Atwood) #9

I don’t think there is any data structure about a post that captures “has an upload”. There are certainly tables of uploads, but not as you’re thinking.


(Mittineague) #10

I’ve toyed with this a very little bit - still in the “vague plugin idea, exploratory” stage.

Using the Data Explorer plugin with

SELECT user_id
  , original_filename
  , url
  , origin
  , filesize 
  , width 
  , height 
FROM uploads 
WHERE user_id > 2 
ORDER BY user_id

displays

* the single NOT NULL origin was an image not from my own computer


(cpradio) #11

But that table alone doesn’t tell you which post the images/uploads belong too, which would be necessary for showing the topics related to the advanced search query parameter.


(Régis Hanol) #12

Actually, there is one and it’s called “PostUpload:wink:


(Tom Newsom) #13

Is there any way to get this to display the actual images? My SQL is just about good enough for

SELECT user_id
  , CONCAT('<img src="https://discourse.southlondonmakerspace.org', url, '">') As Image
  , filesize 
  , created_at
FROM uploads
WHERE user_id > 2
ORDER BY created_at DESC 

But the Image output is sanitised with " marks


(Régis Hanol) #14

I think we need to add a new image type on the data-explorer



(Kane York) #15

Nope, do this:

SELECT user_id
  , CONCAT('<img src="https://discourse.southlondonmakerspace.org', url, '">')
    AS html$Image
  , filesize 
  , created_at
FROM uploads
WHERE user_id > 2
ORDER BY created_at DESC

It’s a weird tagging scheme, but if you have any better ideas…


(Tom Newsom) #16

That works well enough for my purposes :slight_smile:
Although a further refinement is

CONCAT('<img src="https://discourse.southlondonmakerspace.org', url, E'" style = "max-width: 100%\x3B">')

To avoid filling the screen up with the full-res images

and

WHERE DATE(current_date) - DATE(created_at) < 7

to restrict the search to the last week.

so, full query:

SELECT
    CONCAT('<img src="https://discourse.southlondonmakerspace.org', url, E'" style = "max-width: 100%\x3B">')
    AS html$Image,
    created_at
FROM uploads
WHERE DATE(current_date) - DATE(created_at) < 5
ORDER BY created_at DESC 

Ideally, I’d like to restrict the list to uploads that are found in posts with the “regular” archetype (ie. not PMs) but that’s a nested SQL query way out of my depth.


(Tobias Eigen) #17

This is cool - thanks for sharing. I just tried it, and it’s actually showing mostly user profile pics on my site.

It would be helpful to be able to see a link to the image in context - on user profile, PM or post.


(Tom Newsom) #18

Tricky, when the upload itself can appear in multiple contexts. You’d also need a much more complicated query, because that information is spread over multiple tables


(Tobias Eigen) #19

Yeah, I figured as much. It would just be nice to make this info actionable, in case you come across some unwanted content and want to remove it.

I just tried doing a search for an image URL found via your query which did not show any results.


(Tom Newsom) #20

I’m learning to swim quite well :smiley:

Here’s a query that shows non-PM uploads from the last 7 days, alongside a link to the originating post.

SELECT
CONCAT('<a href = "/p/', posts.id, '">', topics.title)
AS html$post,
CONCAT('<img src="', url, E'" style = "max-width: 100%\x3B">')
AS html$Image
FROM post_uploads, uploads, posts, topics
WHERE DATE(current_date) - DATE(uploads.created_at) < 7
AND post_uploads.upload_id = uploads.id
AND topics.archetype = 'regular'
AND post_uploads.post_id = posts.id
AND posts.topic_id = topics.id
ORDER BY uploads.created_at DESC 

I could go on all night, but I think I should sleep and stop bumping this topic…