Nested Lua Integrated Queries

Is there a way to have nested LIQ?
For instance:

${template.each(query[[
  from p = index.tag "gaming-note"
  where <p.page.parentWeek == this.page.name>
  select {name=p.page,item=p.text}
]], templates.pageNameAndItem)}

Can the bit in the where clause be a new query. Is there maybe a better way of getting paragraphs that have the tag “gaming-note” and also the parentWeek of the page is the same as the name of the current page?

Since the bit in the where clause is a regular Lua code, I guess you can simply create a function:

function parentWeekMatches(page)
   return page.parentWeek == editor.getCurrentPage()
end

And then use it:

${template.each(query[[
  from p = index.tag "gaming-note"
  where parentWeekMatches(p.page)
  select {name=p.page,item=p.text}
]], templates.pageNameAndItem)}

I’m not exactly sure what your end goal is, but functions are probably the answer :wink:

I’m not sure what if you want here is nested LIQ (which theoretically should just work, since query[[...] is just an expression, you should be able to nest them), or the ability to follow references? p.page will contain the name of the page as a string, and I suppose what you would want is a lookup of that page by name, and check an attribute of that, right?

This should work, although it will be quite inefficient:

```space-lua
function getPageByName(page)
  local pages = query[[from index.tag "page" where _.name == page]]
  if #pages == 1 then
    return pages[1]
  else
    error("Page not found")
  end
end
```

You can then use it in your where getPageByName(p.page).parentWeek == editor.getCurrentPage()

I mention that it’s inefficient, because it will perform this type of (nested) query for every single game-note object it encounters, which is ok if it’s just like 10, but it starts to add up.

What you would really want is a “real” SQL-style database, and the ability to do joins. But SilverBullet doesn’t support that really, and it’s not really built to be able to do this efficiently.

I was able to get it with this

function getGamingNotes(noteType)
  local pages = query[[from index.tag "page" where parentWeek == editor.getCurrentPage()]]
  if #pages > 0 then
    local paragraphs = query[[from note = index.tag(noteType)]]
    local matchingNotes = {}
    
    -- Create a lookup table for valid page names
    local pageNames = {}
    for _, page in ipairs(pages) do
      pageNames[page.name] = true
    end
    
    -- Single pass through paragraphs
    for _, paragraph in ipairs(paragraphs) do
      if pageNames[paragraph.page] then
        table.insert(matchingNotes, {
          name = paragraph.page,
          item = paragraph.text
        })
      end
    end
    
    return matchingNotes
  else
    error("Page not found")
  end
end

and

${template.each(getGamingNotes("exercise-note"), templates.pageNameAndItem)}

At least its not O(n x m) speed and rather O(n + m)