Skip to content

Instantly share code, notes, and snippets.

@prince-neres

prince-neres/get_articles_with_image_without_alt.sql

Last active July 22, 2025 02:20
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save prince-neres/0b5fbd50194dc1fa36074d713a7e28fc to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save prince-neres/0b5fbd50194dc1fa36074d713a7e28fc to your computer and use it in GitHub Desktop.
Download ZIP
Raw
get_articles_with_image_without_alt.sql
select l.plid, concat('/web', g.friendlyurl, l.friendlyurl)
from journalarticle ja
inner join assetentry ae on ae.classpk = ja.resourceprimkey
inner join portletpreferencevalue ppv on (
(ppv.largevalue like '%' || ae.classuuid || '%')
or
(ppv.name = 'assetEntryId' and (ppv.smallvalue = ae.entryid::TEXT or ppv.largevalue = ae.entryid::TEXT))
or
(ppv.name = 'articleId' and (ppv.smallvalue::TEXT = ja.articleid or ppv.largevalue = ja.articleid))
)
inner join portletpreferences pp on pp.portletpreferencesid = ppv.portletpreferencesid
inner join layout l on l.plid = pp.plid
inner join group_ g on g.groupid = l.groupid
group by l.plid, g.friendlyurl, l.friendlyurl;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment