Forum Discussion

citizenelah's avatar
citizenelah
Ace
6 months ago

API for newly published articles?

I'd like to automate social copy drafts in our social media platform for newly published articles. I can't seem to find the right combination of attributes / tables in a LiQL call OR the right API method that will give me what I'm looking for. I just want the last 10 articles that were published. I get the last updated, which could be older articles, and I published one two days ago that was a draft a month ago. That doesn't show up in my query of 10 at all as it's out of range, unfortunately.

Any advice on how to accomplish this would be appreciated.

The LiQL query I'm using: 

query = f"SELECT id, view_href, body, last_publish_time FROM messages WHERE board.id = 'TechnicalArticles' ORDER BY manual_sort limit 10"
  • citizenelah's avatar
    citizenelah
    6 months ago

    I got it! Had to combine post_time AND manual_sort to get the results to match my latest published article:

    query = "SELECT id, view_href, body, last_publish_time FROM messages WHERE board.id = 'TechnicalArticles' ORDER BY manual_sort, post_time desc limit 10"

     

    Thanks karuna179 for the nudge and the help with post_time!

4 Replies

Sort By
  • karuna179's avatar
    karuna179
    Expert
    6 months ago

    citizenelah 

    You can use this query to fetch the articles if they are sorted manually from the admin structure. 

    SELECT id, view_href, body, last_publish_time FROM messages WHERE board.id = 'TechnicalArticles' ORDER BY manual_sort asc limit 10

    If you need to fetch the latest 10 published articles then you will need the below query:

    SELECT id, view_href, body, last_publish_time FROM messages WHERE board.id = 'TechnicalArticles' ORDER BY last_publish_time desc limit 10

    The last_publish_time field contains the date time that the message was last updated. If there have been no edits to a message, then last_publish_time will equal post_time.

    If you do not want the edited posts then you need to just add the query and sort the posts by post_time in descending order and you will be able to fecth the latest 10 posts published from the board. 

    SELECT id, view_href, body, last_publish_time FROM messages WHERE board.id = 'TechnicalArticles' ORDER BY post_time desc limit 10

    Do let me know if in case I was able to provide you a solution or in case you have some other queries related to the same, will be happy to help. 

  • citizenelah's avatar
    citizenelah
    Ace
    6 months ago

    Neither the last_publish_time nor the post_time ordering gives me articles, they both return discussion messages attached to the articles.

  • citizenelah's avatar
    citizenelah
    Ace
    6 months ago

    I got it! Had to combine post_time AND manual_sort to get the results to match my latest published article:

    query = "SELECT id, view_href, body, last_publish_time FROM messages WHERE board.id = 'TechnicalArticles' ORDER BY manual_sort, post_time desc limit 10"

     

    Thanks karuna179 for the nudge and the help with post_time!