Forum Discussion

peterlu's avatar
peterlu
Champion
7 years ago

API v2 featured posts bug

Hi Lithium,
 
I have tried to add 
WHERE board.id = 'Blog'  AND depth = 0 and conversation.featured = true Order By conversation.featured ASC
 
It throws me an error message

conversation.featured sort can only be combined with  depth = 0 and conversation.featured = true in the WHERE clause

 
It is saying that I cannot add board.id = 'Blog' in where clause
 
I think this is a LITHIUM API core bug.
 
We only want features posts from 1 particular board, but not the whole community
 
Lithium Doc says
 conversation.featuredSort by order defined for Featured Threads as defined in Community Admin > Content > Featured Threads > Ordering.17.9
SELECT topic.subject, kudos FROM messages WHERE conversation.featured = true AND depth=0 Order By conversation.featured ASC
 
Peter
  • VikasB's avatar
    VikasB
    Boss
    7 years ago

    peterlu Before 17.9 it was not supported with any of the constraints in where clause.
    But now it can be combined with depth = 0 and conversation.featured = true in the WHERE clause but lithium does not support "conversation.featured = true " with other constraints in the WHERE clause. 

    But you can achieve it using two queries

    SELECT id FROM messages WHERE conversation.featured = true AND depth=0 order by conversation.featured ASC limit ${count}

     Collect all the ids in an array in such format so it can be passed in IN query.

    SELECT topic.subject, kudos FROM messages WHERE id IN (${ids}) and board.id = 'ID'

    It would fetch all the required posts in the desired order. 

     

    • peterlu's avatar
      peterlu
      Champion
      7 years ago
      VikasB this won't work. id IN ('222', '111', '333') will print out '111', '222', '333', thus the ordering won't work. Besides, In API v1, you can add board id into the query. API v2 should have it. Hacking it is not the proper way :) Thanks anyway :)