About
page sql is a SELECT sql syntax that permits to select pages and return page attribute used in a template iterator.
Syntax
Select
A page select should follow the following railroad diagram.
'SELECT' [ from ] \
[ 'WHERE' predicate {',' predicate } ] \
[ ('ORDER BY' attribute ('ASC'|'DESC')? {',' attribute ('ASC'|'DESC')?}) | 'ORDER RANDOM' ] \
[ limit 'Number' ] ;
From
The from clause permits to set where the page data comes from.
' FROM' ('pages'|'backlinks'|'descendants')
where:
- pages is the default table (ie all pages of the website)
- backlinks are the backlink pages. Pages that points to the actual requested page with a link.
- descendants are the descendants page. Pages that have the same or a higher 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 page attribute
Predicate
A predicate is a filter that will reduce the set of pages and takes the below form:
filter-attribute ( ('='|'!=') 'value' || ['NOT'] ('GLOB' glob-pattern | 'LIKE' like-pattern ['ESCAPE' 'char']))
filter-attribute
If your sqlite does not support Json, you can't filter on manually added metadata, otherwise you can filter on all attributes
glob-pattern
GLOB pattern matching is case sensitive and uses the 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 path and title of all pages that ends with 3 digits.
select where path glob '*[0-9][0-9][0-9]'
This sql returns the following data for this website.
| Page |
|---|
| :docs:router:404 - What is a missing page (404) and how ComboStap solve them |
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:
- that have the terms image, svg or raster in their path
you would use the following page sql:
select where path like '%image%' or path like '%svg%' or path like '%raster%'
This page sql will return the following pages for this website.
| Pages | Title |
|---|---|
| :docs:content:image | How to use the image tag in ComboStrap |
| :docs:content:page-image | Page-image Component: Renders the illustrative image of your page |
| :docs:content:raster | Raster Image in ComboStrap (jpg, png, ...) |
| :docs:content:svg | How to use a SVG in ComboStrap |
| :docs:page:featured-image | Featured Image |
| :docs:page:first-image | First image |
| :release:deprecated:images-meta | The page image metadata |
| :docs:performance:svg_optimization | Svg Optimization included |
| :howto:image:same_dimension | How to get a serie of images with the same dimension (Width and Height) ? |
| :howto:image_center | How to center an image ? |
date predicate
You can use:
- the now keyword
- the date function and the datetime function (They follow the same usage than Sqlite)
Example:
select from pages where date_end >= now order by date_start asc
- All pages modified last week
select from pages where date_modified >= date('now','-7 days') order by date_modified asc
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 path, you would execute this query:
select order by date_modified desc limit 4
This sql returns the following pages for this website.
| Pages | Date Modified |
|---|---|
| :docs:admin:cli | Thursday, November 28, 2024 |
| :docs:admin:docker | Thursday, November 28, 2024 |