How to define your data set with Sql

ComboStrap - Page Sql

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,,,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]'
SQL

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:

  • the path and title of all pages
  • 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%'
SQL

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
SQL
  • All pages modified last week
select from pages where date_modified >= date('now','-7 days') order by date_modified asc
SQL

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
SQL

This sql returns the following pages for this website.

Pages Date Modified
:docs:admin:cli Monday, February 24, 2025
:docs:admin:docker Monday, February 24, 2025




Showcase yourself and your brand

Get free news, tips, and tricks
to create a remarkable experience for your readers.




Related HowTo's


Recommended Pages



Task Runner