---json
{
    "canonical": ":page_sql",
    "description": "This page defines the sql that can be used to retrieve data from pages",
    "low_quality_page": "false",
    "name": "Page Sql",
    "page_id": "5fkwj0m6w4mhjmt4nqm7w",
    "title": "How to define your data set with Sql"
}
---
====== ComboStrap - Page Sql ======


===== About =====
''page sql'' is a ''SELECT'' <icon name="carbon:SQL"/> sql syntax that permits to select pages and return [[docs:templating:variable|page attribute]] used in a [[docs:templating:iterator|template iterator]].






===== Syntax =====

==== Select ====
A page select should follow the following [[docs:diagram:railroad|railroad diagram]].

<railroad>
<code bnf display="none">
'SELECT'  [ from ] \
[ 'WHERE' predicate {',' predicate } ] \
[ ('ORDER BY' attribute ('ASC'|'DESC')? {',' attribute ('ASC'|'DESC')?}) | 'ORDER RANDOM' ] \
[ limit 'Number' ] ;
</code>
</railroad>

==== From ====
The ''from'' clause permits to set where the page data comes from.

<railroad>
<code bnf display="none">
' FROM' ('pages'|'backlinks'|'descendants') 
</code>
</railroad>
where:
  * ''pages'' is the default table (ie all pages of the website)
  * ''backlinks'' are the [[docs:navigation:backlink|backlink pages]]. Pages that points to the actual requested page with a link.
  * ''descendants'' are the [[docs:page:descendants|descendants page]]. Pages that have the same or a higher [[docs:page:level|level]] than the current page.

The ''from'' clause is is facultative. The selection will use the ''pages'' table if the from clause is not found.
==== Attribute ====
The ''attribute'' are defined in the [[docs:templating:variable|page attribute]]

==== Predicate ====

A predicate is a filter that will reduce the set of pages and takes the below form:
<railroad>
<code bnf display="none">
filter-attribute  ( ('='|'!=')  'value' ||  ['NOT'] ('GLOB' glob-pattern | 'LIKE'  like-pattern  ['ESCAPE' 'char']))
</code>
</railroad>


=== filter-attribute ===
If your [[docs:analytics:query##json_support|sqlite does not support Json]], you can't filter on manually added metadata, otherwise you can filter on all [[variable|attributes]]


=== glob-pattern ===

''GLOB'' pattern matching is case sensitive and uses the [[kb>glob|Unix file globbing syntax]] for its wildcards:
      * ''*'' matches any number of characters (including none)
      * ''?'' matches exactly one character.
      * ''[]'' specify a set of single characters or, when the hyphen character ''-'' is used, a range of characters. Example: ''[aeiou]'' matches any lowercase vowel, ''[0-9]'' matches any digit.
      * the backslash character ''\'' is the escape character. ie ''\\'' matches a single backslash, ''\?'' matches the question mark.
       * any other characters match itself.
Subpatterns are not supported by Sqlite (ie ''{sun,moon,stars}'' to match ''sun'', ''moon'', or ''stars'')

**example:** the [[docs:page:system:path|path]] and [[docs:page:title|title]] of all pages that ends with 3 digits.

<code sql>
select where path glob '*[0-9][0-9][0-9]'
</code>
<webcode name="Default" frameborder="0">
<code dw display="none">
<iterator>
<data>
select where path glob '*[0-9][0-9][0-9]'
</data>
^ Page ^
<fragment>
| [[$path|$path]] - $title |
</fragment>
</iterator>
</code>
This sql returns the following data for this website.
</webcode>


=== like-pattern ===

The ''LIKE'' is not case sensitive and in the pattern expression:
      * ''%'' matches any sequence of zero or more characters
      * ''_'' matches any single character.
      * any other characters match itself.




**example:** If you want to get:
  * the [[docs:page:system:path|path]] and [[docs:page:title|title]] of all pages
  * that have the terms ''image'', ''svg'' or ''raster'' in their [[docs:page:system:path|path]]
you would use the following page sql:
<code sql>
select where path like '%image%' or path like '%svg%' or path like '%raster%'
</code>

<webcode name="Default" frameborder="0">
<code dw display="none">
<iterator>
<data>
select where path like '%image%' or path like '%svg%' or path like '%raster%'
</data>
^ Pages ^ Title ^
<fragment>
| [[$path|$path]] | $title |
</fragment>
</iterator>
</code>
This page sql will return the following pages for this website.
</webcode>

=== date predicate ===

You can use:
  * the ''now'' keyword
  * the ''date'' function and the ''datetime'' function (They follow the same usage than [[https://www.sqlite.org/lang_datefunc.html|Sqlite]])

Example:
  * All [[:howto:iterator-list-event|active event pages]]
<code sql>
select from pages where date_end >= now order by date_start asc
</code>
  * All pages modified last week
<code sql>
select from pages where date_modified >= date('now','-7 days') order by date_modified asc
</code>


==== Limit ====
The ''limit'' clause permits to return the first rows. It's mostly used in ''Recent'' statement such as the last pages recently modified or created.


Example: If you want to get the last 4 pages along with the modification date and the [[docs:page:system:path|path]], you would execute this query:

<code sql>
select order by date_modified desc limit 4
</code>

<webcode name="Default" frameborder="0">
<code dw display="none">
<iterator>
<data>
select order by date_modified desc limit 4
</data>
^ Pages ^ Date Modified ^
<fragment>
| [[$path|$path]] | ${date_modified|format()} |
</fragment>
</iterator>
</code>
This sql returns the following pages for this website.
</webcode>