Forum Discussion

peterlu's avatar
peterlu
Champion
8 years ago

API v2 operator "NOT IN"

Hi Lithium,

 

When can we have this function for the API v2?

For example, Customers would like to query all messages except for few boards.

 

Peter 

7 Replies

  • him_varma's avatar
    him_varma
    Advisor
    8 years ago

    peterlu

    You can achieve this by custom freemarker function. Firstly you can store all the boards IDs that you do not want to show in a freemarker array. Please see below code:

    <#assign boards_id = ['board_id_1','board_id_2','board_id_3'] />

    After that you can make a rest api v1 or v2 call to fetch all messages and run through loop and also check if the board id exists or not. In my case I am using V2 call. See below code:

    <#assign messages = rest("2.0", "/search?q=" + "select * from messages"?url).data.items />
    <#list messages as message>
      <#if !boards_id?seq_contains(message.board.id)> <!-- checks if board id is not in mentioned IDs array -->
       <!-- display message's content other than boards you do not want to show -->
      </#if>
    </#list>


    Please give KUDOS if you find this helpful. Mark as Accepted Solution in case of solution you were looking for.

  • peterlu's avatar
    peterlu
    Champion
    8 years ago
    him_varma, your code won't work in a pagination environment. And it won't work in some situations as well. NOT IN solution is the ultimate way just like the good old database days.
  • him_varma's avatar
    him_varma
    Advisor
    8 years ago

    peterlu

     

    If we achieve this through custom functionality then we certainly be handling the other cases through custom functionality only. We can build custom pagination using an endpoint to fetch results page wise that will have the same logic as I mentioned above. 

    As long as we do not have the NOT IN operator we could use customizations to achieve the desired functionality :)

    Please give KUDOS if you find this helpful. Mark as Accepted Solution in case of solution you were looking for.

  • peterlu - 

     

    This query would work in pagination environment. 

     

    select * from messages where board.id in ('board1,board2')


    Give kudos if you find my posts helpful or mark solution if it answers your query

    Tariq

  • peterlu's avatar
    peterlu
    Champion
    8 years ago
    TariqHussain yes, your query works. But if a customer has 100+ boards, and they just want to ignore (NOT IN) 1 board only. It is not a good idea to do board.id in ('board1', 'board2'..........'board300')