Yes, I’m a muppet. This works. Nicely done Tom.
Show media uploads to Discourse
Aces
That SQL stuff is powerful voodoo
SELECT
CONCAT('https://discourse.southlondonmakerspace.org/p/', posts.id) AS post,
CONCAT('https://discourse.southlondonmakerspace.org', uploads.url) AS 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’ve temporarily bumped it up to 150 days so you can trawl recent history
Ah, any chance of user name in there too? Would be good to credit.
FWIW, this is the Python to get the data. Missing the accept header threw me for a loop.
#! /usr/bin/env python3
import sys
from urllib.request import Request, urlopen
from urllib.parse import urlencode
import json
if len(sys.argv) != 2:
sys.exit('API key missing')
SLMS_DISCOURSE_QUERY_URL = 'https://discourse.southlondonmakerspace.org/admin/plugins/explorer/queries/1/run'
SLMS_DISCOURSE_VALUES = { 'api_key': sys.argv[1], 'api_username': 'makerspace'}
data = urlencode(SLMS_DISCOURSE_VALUES)
request = Request(SLMS_DISCOURSE_QUERY_URL,
data=data.encode('utf-8'),
headers={
'Content-Type':'application/x-www-form-urlencoded;charset=utf-8',
'Accept': '*/*'
},
)
with urlopen(request) as f:
result = f.read().decode('utf-8')
result = json.loads(result)
Ok, this also outputs usernames now
Pfft. It’s probably nicer code than my amateur-hour SQL
Heh. One more request. Haven’t seen any EXIF date info yet, and the creation date is my download. I’d wager these aren’t originals (resized and/or just stripped).
There’s more media types than just photos tho’, so regardless of whether they’re originals or not, could you add a column for posted date pretty please?
Done. On my phone. On the bus.
Close but no cigar?
Columns are –
['html$user', 'html$post', 'html$image']
You’re requesting query no. 6?
Looks like you’re asking for no.1
Query #6 indeed has the date (when you change query number?). And it tidies up the html cruft I had to work around, even better. But… now the post title has disappeared.
You just want the title, or the link to the post as well?
I just used the title for the file name. Long term, both might be handy.
(and realising what’s happened, I extracted URL and title from the HTML link)
how about now then?
Flickr now has 53 new photos, and some lovely @marketing can be done.
(It’s also just got five project posters back from E&C Mini Maker Faire, that I realised weren’t there when I went to link to Yulia’s jewellery)
Speaking of this, I got behind on my Flickr-updating, and I’ve got a gap. I’ll need the request period increased or a special one-off, but hadn’t posted to say so as I’ve been busy with other things. Its… (goes to check)
…I’m missing
2016-12-05-KylieJudd---Lasercut_lamp
to
2017-01-08-Jonathan---14_hour_flight_with_a_2_and_4_year_old