Forum Discussion

Han's avatar
Han
Ace
7 years ago

Get all latest posts for a user

Hi all,

I'd like to create a custom "latest contributions" component for profile pages. I'm trying to select all latest posts for a user with API V2. 

At the moment, I'm always getting the same 5 posts back, no matter which user id I am viewing. Here is what I have so far

SELECT * FROM messages WHERE depth = 0 AND author.id = 'user.id' ORDER BY conversation.last_post_time DESC LIMIT 10


I'm quite new to using API V2, so any help here is appreciated. 

  • luk's avatar
    luk
    7 years ago

     

    Now we are talking freemarker which of course changes things =)... So with the code from Parshant it would be clear why you get always the same result, because you're asking for the last 10 topics of the currently logged in user, e.g. yourself (which is what ${user.id} will give you), I think what would be correct for your scenario is the page.context.user context-object which according to the docs (here) state:

    If this page is associated with a user, then this returns a user context object, otherwise returns null. You can chain calls on the user object from this method. The following pages return a user:
    - MyProfilePage (only when an admin is editing another user's profile. When the current user is editing their profile use the $user object instead)
    - ViewProfilePage
    - MobileViewProfilePage
    - TkbUserContributedArticlesPage

    I assume you're targeting the "ViewProfilePage" quilt with  your custom component,it would work as follows with your query:

    SELECT * FROM messages WHERE depth = 0 AND author.id = '${page.context.user.id}' ORDER BY conversation.last_post_time DESC LIMIT 10

    BUT, careful, as the docs say, if this component was placed on a page with no user context, your query would fail, therefore I'd check if page.context.user exists before setting off the API request, e.g.

    <#if (page.context.user)?has_content>
    // whatever you want to do here
</#if>

    hope that helps!

13 Replies

Sort By
  • luk's avatar
    luk
    Boss
    7 years ago

    Han not sure what's going wrong, but your query seems to work for me, e.g.

    SELECT * FROM messages WHERE depth = 0 AND author.id = '3' ORDER BY conversation.last_post_time DESC LIMIT 10

// and

SELECT * FROM messages WHERE depth = 0 AND author.id = '8' ORDER BY conversation.last_post_time DESC LIMIT 10

    return

    a) 10 results (not 5) and

    b) different results for both queries

  • Parshant's avatar
    Parshant
    Boss
    7 years ago

    Hi Han,

    Can you try copy and paste using below api again.

    SELECT * FROM messages WHERE depth = 0 AND author.id = '${user.id}' ORDER BY conversation.last_post_time DESC LIMIT 10

     Let me know the response on this.

  • luk's avatar
    luk
    Boss
    7 years ago

     

    Now we are talking freemarker which of course changes things =)... So with the code from Parshant it would be clear why you get always the same result, because you're asking for the last 10 topics of the currently logged in user, e.g. yourself (which is what ${user.id} will give you), I think what would be correct for your scenario is the page.context.user context-object which according to the docs (here) state:

    If this page is associated with a user, then this returns a user context object, otherwise returns null. You can chain calls on the user object from this method. The following pages return a user:
    - MyProfilePage (only when an admin is editing another user's profile. When the current user is editing their profile use the $user object instead)
    - ViewProfilePage
    - MobileViewProfilePage
    - TkbUserContributedArticlesPage

    I assume you're targeting the "ViewProfilePage" quilt with  your custom component,it would work as follows with your query:

    SELECT * FROM messages WHERE depth = 0 AND author.id = '${page.context.user.id}' ORDER BY conversation.last_post_time DESC LIMIT 10

    BUT, careful, as the docs say, if this component was placed on a page with no user context, your query would fail, therefore I'd check if page.context.user exists before setting off the API request, e.g.

    <#if (page.context.user)?has_content>
    // whatever you want to do here
</#if>

    hope that helps!

  • Han's avatar
    Han
    Ace
    7 years ago

    Thanks luk, it was the way I was getting the user id and not the query itself. I now get the right results when looking at another users page.

    However, it's not displaying more than 5 results for my own profile, and not getting the latest posts I've made. Do you by any chance know why that might be happening? 

  • luk's avatar
    luk
    Boss
    7 years ago

    Han not able to tell from the query only, are you able to share the entire code?

  • Han's avatar
    Han
    Ace
    7 years ago

    luk sure thing, here is what I have: 

    <#assign pageNumber = webuisupport.path.parameters.name.get("page", 1) /> 
<#assign pageSize = 10 /> 
<#assign offset = (pageNumber - 1)*pageSize />

<#if (page.context.user)?has_content>
  <#assign rest_query = "SELECT * FROM messages WHERE depth = 0 AND author.id = '${page.context.user.id}' ORDER BY conversation.last_post_time DESC LIMIT ${pageSize} OFFSET ${offset}"/>
  <section class="profile-latest-contributions">
    <@component id="forums.widget.message-list-panel" messages="rest_2.0:/search?q="+rest_query?url style="wide" numMessages="conv:"+pageSize />
  </section>    

  <#-- Offset for pager -->
  <#assign queryCount = "SELECT count(*) FROM messages WHERE depth = 0 AND author.id = '${page.context.user.id}'" />            
  <#assign totalMessages = (rest("2.0", "/search?q=" + queryCount?url).data.count)!0 />
  <#if (offset >= totalMessages) || (offset < 0) >
    <#assign pageNumber = 1 />
    <#assign offset = 0 />
  </#if>

  <#assign pageable_item = webuisupport.paging.pageableItem.setCurrentPageNumber(pageNumber).setItemsPerPage(pageSize).setTotalItems(totalMessages).setPagingMode("enumerated").build />
  <@component id="common.widget.pager" pageableItem=pageable_item />
</#if>
  • luk's avatar
    luk
    Boss
    7 years ago

    Just tried your code, and so far it seems to work, for other users as well as for my own... I assume you checked your own topic count (via API Browser in Studio)?

    SELECT count(*) FROM messages WHERE depth = 0 AND author.id = '<your.user.id>' ORDER BY conversation.last_post_time DESC LIMIT 1000

     and the result is greater than 5? 

  • Han's avatar
    Han
    Ace
    7 years ago

    luk, the result is saying 6. I know there is more posts than that. Which is what leads me to think I'm using the wrong query. 

  • luk's avatar
    luk
    Boss
    7 years ago

    Posts maybe, but topics you started (that's what depth = 0 does), e.g. you were the initial author?

  • Han's avatar
    Han
    Ace
    7 years ago

    I'd like to have it  return both topics started, and then any replies/posts for each user. I've already tried removing the depth clause. But it just gets an error.

    Do I need to have multiple queries?