Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Include press and extra photos in the Atom feed #16

Closed
simonw opened this issue Dec 26, 2019 · 2 comments
Closed

Include press and extra photos in the Atom feed #16

simonw opened this issue Dec 26, 2019 · 2 comments
Labels

Comments

@simonw
Copy link
Owner

@simonw simonw commented Dec 26, 2019

No description provided.

@simonw simonw added the enhancement label Dec 26, 2019
@simonw

This comment has been minimized.

Copy link
Owner Author

@simonw simonw commented Dec 26, 2019

After a huge amount of experimentation, this query seems to do the trick:

SELECT
  'tag:niche-museums.com,' || substr(m.created, 0, 11) || ':' || m.id as atom_id,
  m.name as atom_title,
  m.created as atom_updated,
  'https://www.niche-museums.com/browse/museums/' || m.id as atom_link,
  coalesce(
    '<img src="' || m.photo_url || '?w=800&amp;h=400&amp;fit=crop&amp;auto=compress">',
    ''
  ) || render_markdown(
    m.description || '

' || coalesce(
      (
        select
          group_concat(
            '* [' || json_extract(p.value, '$.title') || '](' || json_extract(p.value, '$.url') || ') ' || json_extract(p.value, '$.author') || ', ' || json_extract(p.value, '$.publication') || ', ' || json_extract(p.value, '$.date'),
            '
'
          )
        from
          json_each(coalesce(m.press, '[{}]')) as p
      ),
      ''
    )
  ) || coalesce(
    (
      select
        group_concat(
          '<p><img src="' || json_extract(ph.value, '$.url') || '?w=400&auto=compress"></p>',
          ''
        )
      from
        json_each(coalesce(m.photos, '[{}]')) as ph
    ),
    ''
  ) as atom_content_html
FROM
  museums m
order by
  m.created desc
limit
  15
simonw added a commit that referenced this issue Dec 26, 2019
simonw added a commit that referenced this issue Dec 26, 2019
@simonw

This comment has been minimized.

Copy link
Owner Author

@simonw simonw commented Dec 26, 2019

Done. Here's what it looks like in Reeder on OSX:

Screenshot_12_26_19__6_19_PM

@simonw simonw closed this Dec 26, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
1 participant
You can’t perform that action at this time.