List of all blog articles across the Community, but excepting one or more categories

Hi Warren_Brill,

what you need in this case is an API request looking like this:

SELECT * FROM messages WHERE board.id NOT IN ('xxxx', 'yyyy')

Of course with some additional filters for blog post type etc.

Unfortunately, APIv2 doesn't have a NOT_IN operator. 😞 I already created an idea for this in the past.

Up to the point where API gets such an operator, you can implement things using a white-list of boards/categories you want to query. Therefore create a Freemarker list with IDs you want to query:

<#assign boards = ["id_1", "id_2"] />

Then use this list as parameter in the query:

SELECT * FROM messages WHERE board.id IN ('${boards}')

Best regards,

Christian

Thanks Christian,
I'm working with this as a base. Question:
For

<#assign boards = ["id_1", "id_2"] />

 are "id_1" and "id_2" actual node IDs (numeric, as found in LSI) or the alphanumeric IDs used in Community Structure?

This would make ORDER BY feasible.

Warren_Brill  - use the node IDs from your community structure. These are the technical names for your nodes. 

So by "technical names", you mean the actual, alphanumeric IDs, not the fully numeric Node IDs as used in LSI. Every node has at least three identifiers: a title, an ID, and a numeric ID. I read your reply as referring to the middle one of the three as the technical name. Thanks.

Yes Warren_Brill, you're right. 😊

So it's been ten years since I did any serious SQL programming, and it appears that FreeMarker isn't going to make this easy. I created a sort of a hybrid component, to set up an array (I just put three elements in for testing). Just to verify that the array elements are actually the node (blog) IDs I want, I echoed the elements. I then tried two versions of the query, one just the query, and the other using a <#assign to create an output variable. I commented one out, then the other (see below), but they both generate the same FreeMarker error messaging. I'm sure what's wrong is that I've been away so long, and I'm not writing ASP.NET or something like that. Here's the initial snippet:

<#assign whiteList = ["alliancesblog", "AroundtheStorageBlock", "BehindthescenesatLabs"] />

<h3>List of all current blog articles</h3>
${whiteList[0]}<br />${whiteList[1]}<br />${whiteList[2]}

<#-- SELECT * FROM messages WHERE blog.id IN '${whiteList}' -->
<#assign articleList = "SELECT * FROM messages WHERE blog.id IN '${whiteList}'" />
${articleList}

No way am I asking for code writing - just a gut check if I can save this.

I'm not terribly impressed with the documentation on Lithosphere, but then, I was a technical writer for long time before moving into the web in mid 90s.

Warren_Brill  - I recommend putting in the received error as well, to make debugging a bit easier.

But after a quick look at your queries, I'm sure you use a wrong attribute blog.id.

<#assign articleList = "SELECT * FROM messages WHERE blog.id IN '${whiteList}'" />

The messages resource of APIv2 doesn't have any attribute named blog. You should replace blog.id with board.id:

<#assign articleList = "SELECT * FROM messages WHERE board.id IN '${whiteList}'" />

See description of the board attribute below:

"The board in which the message appears. A board can be of type: forum, tkb, qanda, blog, idea, contest, group, or review. The conversation style is derived from the board type of the board that contains the message."

For information see message resource docs.

Thanks - I will go that direction and see where it leads me.

Warren_Brill Depending on your community structure, it might be even easier. Are your Blog-Archives (or other nodes you want to exclude) outside of the main category/categories of your community, e.g. something like

community
  |-main-category
  |   |-some-blog-node
  |   |-another-category
  |-blog-archive
  |-another-excluded-node

if you have it structured like that you can use the ancestors.id constraint as a filter without having to deal with a hardcoded white-list.

The node structure reflects product hierarchies, so blogs live within product categories, along with their product board brethren.