Display images associated with messages
We're working on a featured content carousel to pull featured messages from different areas of our site. As part of the carousel, we'd like to include the first image associated with each message. I have the SELECT statement and Freemarker <#list> set up to display the other information associated with the featured messages (subject, teaser, view_href, etc.), but I can't figure out how to retrieve the first image associated with each post. I know that you can SELECT images as part of the query, but that only returns an objectInstance.
Is there a way to drill into the images object to return the details of a given image? I would have expected that images would have returned an array, and that I could then use images[0] to get the details of the first image.
Any help would be greatly appreciated. Here's what I have thus far:
<#assign boardID = "discussions"/> <#assign apiVersion = "2.0"/> <#assign featuresQuery = "SELECT id, conversation.featured, conversation.style, conversation.last_post_time_friendly, view_href, subject, author, search_snippet, images FROM messages WHERE conversation.featured = true AND board.id='" + boardID + "'"/> <#assign featuredItems = rest(apiVersion, "/search?q=" + featuresQuery?url + "&restapi.response_style=view").data.items![] />
<ul> <#list featuredItems as features> <li><a href="${features.view_href}">${features.subject}</a><br/> ${features.search_snippet} <#assign imageQuery = "SELECT count(*) FROM images WHERE message.id='" + features.id + "'"/> <br/> <!--Here's where I'm stuck; I'd like to parse details of the first image associated with this message-->
${features.images} </li> </#list> </ul>
Hi,
Don't know if answer is still needed but here is a solution. Just change the query
<#assign imageQuery = "SELECT count(*) FROM images WHERE message.id='" + features.id + "'"/>
in
<#assign imageQuery = "SELECT * FROM images WHERE message.id='" + features.id + "' LIMIT 1"/>
then you will have to parse the answer to get image url in the format which suits your ui needs.
<#assign imagesItems = rest(apiVersion, "/search?q=" + imageQuery?url + "&restapi.response_style=view").data.items![] />
<#assign imageUrl = imageItems[0].large_href />I created a macro to render image by dimensions and message id
<#macro getMessageImage imageDimensions messageId> <#assign messageImageURL = "" /> <#assign messageImageDescription = "" /> <#assign coverImageQuery = rest("2.0","/search?q=" + "SELECT cover_image FROM messages WHERE id = '${messageId}'"?url) /> <#if coverImageQuery.data.items[0].cover_image?? > <#assign messageImageId = "${coverImageQuery.data.items[0].cover_image.id}" /> <#assign messageImageWidth = "${coverImageQuery.data.items[0].cover_image.width}" /> <#assign messageImageHeight = "${coverImageQuery.data.items[0].cover_image.height}" /> <#switch imageDimensions> <#case "tiny"> <#assign messageImageURL = "${coverImageQuery.data.items[0].cover_image.tiny_href}" /> <#break> <#case "small"> <#assign messageImageURL = "${coverImageQuery.data.items[0].cover_image.small_href}" /> <#break> <#case "medium"> <#assign messageImageURL = "${coverImageQuery.data.items[0].cover_image.medium_href}" /> <#break> <#case "large"> <#assign messageImageURL = "${coverImageQuery.data.items[0].cover_image.large_href}" /> <#break> <#case ""> <#assign messageImageURL = "${coverImageQuery.data.items[0].cover_image.original_href}" /> <#break> <#default> <#assign messageImageURL = "/t5/image/serverpage/image-id/${messageImageId}/image-dimensions/${imageDimensions}/crop-image/true?v=v2" /> </#switch> <#if coverImageQuery.data.items[0].cover_image.description !=""> <#assign messageImageDescription = "${coverImageQuery.data.items[0].cover_image.description}" /> <#else> <#assign messageImageDescription = "${coverImageQuery.data.items[0].cover_image.title}" /> </#if> <#else> <#assign messageImagesQuery = rest("2.0","/search?q=" + "SELECT * FROM images WHERE messages.id = '${messageId}' LIMIT 1"?url) /> <#if messageImagesQuery.data.size gt 0> <#assign messageImageId = "${messageImagesQuery.data.items[0].id}" /> <#assign messageImageWidth = "${messageImagesQuery.data.items[0].width}" /> <#assign messageImageHeight = "${messageImagesQuery.data.items[0].height}" /> <#assign messageImageheight = "${messageImagesQuery.data.items[0].height}" /> <#switch imageDimensions> <#case "tiny"> <#assign messageImageURL = "${messageImagesQuery.data.items[0].tiny_href}" /> <#break> <#case "small"> <#assign messageImageURL = "${messageImagesQuery.data.items[0].small_href}" /> <#break> <#case "medium"> <#assign messageImageURL = "${messageImagesQuery.data.items[0].medium_href}" /> <#break> <#case "large"> <#assign messageImageURL = "${messageImagesQuery.data.items[0].large_href}" /> <#break> <#case ""> <#assign messageImageURL = "${messageImagesQuery.data.items[0].original_href}" /> <#break> <#default> <#assign messageImageURL = "/t5/image/serverpage/image-id/${messageImageId}/image-dimensions/${imageDimensions}/crop-image/true?v=v2" /> </#switch> <#if messageImagesQuery.data.items[0].description !=""> <#assign messageImageDescription = "${messageImagesQuery.data.items[0].description}" /> <#else> <#assign messageImageDescription = "${messageImagesQuery.data.items[0].title}" /> </#if> </#if> </#if> </#macro>
