Forum Discussion

fctester123456's avatar
fctester123456
Adept
7 years ago

LIQL: using query result as in condition for another

is there any way to use result from first query as condition in second query.

e.g.

select id from boards where parent_category.id ='xxxxx'

this query return id list : aaa ,bbb ,ccc

 

i want to get all message in the 3 board 

select * FROM messages WHERE board.id in ( id list from first query ) 

instead of 

select * FROM messages WHERE board.id in ( 'aaa' ,'bbb' ,'ccc' ) 

 

I plan to loop the query 1 and query 2 for different parent category.

  • TariqHussain's avatar
    TariqHussain
    7 years ago

    fctester123456- You cannot run the nested query inside in LIQL. However, you can generate strings from the first query which can be used inside the second query.

     

    e.g ('board-id1', 'board-id-2')

     

     

    <#assign boardsQuery =restadmin("2.0","/search?q=" + "SELECT * FROM boards"?url).data.items />

<#assign boardIDs = "" />
<#list boardsQuery as board>
  <#if (board?index + 1) == userid?size>
        <#assign boardIDs = boardIDs+ "'" + board.id + "'" />
  <#else>
        <#assign boardIDs = boardIDs+ "'" + board.id + "'" +"," />
  </#if>
</#list>

    The above query will return you string  'board-id1', 'board-id-2' which further can be used in the second query.

     

     

    <#assign messagesQuery =restadmin("2.0","/search?q=" + "SELECT * FROM messages where board.id in ( ${boardIDs} ) "?url).data.items />

     

     

     

  • TariqHussain's avatar
    TariqHussain
    Boss
    7 years ago

    fctester123456- You cannot run the nested query inside in LIQL. However, you can generate strings from the first query which can be used inside the second query.

     

    e.g ('board-id1', 'board-id-2')

     

     

    <#assign boardsQuery =restadmin("2.0","/search?q=" + "SELECT * FROM boards"?url).data.items />

<#assign boardIDs = "" />
<#list boardsQuery as board>
  <#if (board?index + 1) == userid?size>
        <#assign boardIDs = boardIDs+ "'" + board.id + "'" />
  <#else>
        <#assign boardIDs = boardIDs+ "'" + board.id + "'" +"," />
  </#if>
</#list>

    The above query will return you string  'board-id1', 'board-id-2' which further can be used in the second query.

     

     

    <#assign messagesQuery =restadmin("2.0","/search?q=" + "SELECT * FROM messages where board.id in ( ${boardIDs} ) "?url).data.items />

     

     

     

  • luk's avatar
    luk
    Boss
    7 years ago

    I think it should be possible like Tariq mentioned, you could do something like this:

     

    <#assign query = "SELECT id FROM boards WHERE parent_category.id ='<id>'"?url />
<#assign response = rest("2.0", "/search?q=" + query).data.items /> // fetch the board ids
<#assign boardids = [] /> // initialize variable that will hold our board ids
<#list response as boardid> // loop trough the result and build a sequence from the the hashes provided by the api, e.g. a single hash = {"id": "<yourboardid>"}
    <#if (!boardids?seq_contains)> // just checking if not already added
        <#assign boardids = boardids + [boardid] /> // merge existing with new board id
    </#if>
</#list>

// then we can continue building the next query
<#assign query = "SELECT * FROM messages WHERE board.id IN(boardids?join(","))"?url />
<#assign response = rest("2.0", "/search?q=" + query).data.items />

// your response variable now should hold 25 messages from the boards fetched earlier, you can loop trough them like this
<#list response as post>
    // print the post subject
    <h2>${post.subject}</h2>
</#list>

    code is untested, but something like this will do the job =)...good luck!