How to define your data set with 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 {',' 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:

  • 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%'

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:performance:svg_optimization Svg Optimization included
:docs:page:image What are the illustrative images for a page?
:docs:page:featured-image Featured Image
:docs:page:first-image First image
: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
:release:1.25.3 Thursday, March 28, 2024
:docs:lists:lists Wednesday, March 27, 2024
:docs:navigation:navigation Wednesday, March 27, 2024
:docs:navigation:hierarchical_breadcrumb Wednesday, March 27, 2024




Showcase yourself and your brand

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




Related HowTo's
Undraw Animating
How can I generate a list of pages?

This howto shows you how you can generate a list of pages with the iterator components. page sql a list element with a link created from: the path and the title variables ...
Undraw Animating
How to generate a list of events?

This howto shows you how you can generate a list of pages event with the iterator components. page sql a list element with the date_start and date_end formated date of a page event...
Undraw Animating
How to generate and layout a list of pages with a grid and their image?

This howto shows you how to use the page sql, a fragment, the iterator and to generate a grid layout.


Recommended Pages
Backlink Action
Backlinks management in ComboStrap

Backlinks are important because they give extra-navigation possibilities and gives also more weight to pages. This article tell you everything about backlinks in ComboStrap
Undraw My Documents
Descendant Pages

Descendant pages are pages that have the same or a higher level than the current page in the page system. The descendant's pages can be queried to create an illustrative list in index pages. You can...
Undraw Website Builder Re Ii6e
How to create a carrousel layout

The carrousel component permits to layout and to scroll components horizontally.
Undraw My Documents
Page System

The page system is a system that organize the pages in a hierarchical fashion. With the page path :foo:bar:world its name is the default page name world its container is the namespace :foo:bar:...
Page Explorer List Name To Ui Region
Page-explorer: adding page navigation easily to your website

With the page-explorer component, you can add an explorer like panel that will list your pages in a list or collapsible tree fashion
Undraw My Documents
Replication

Combostrap replicates data from pages: into the database for different functionality such as: index Routing Page sql into the frontmatter (if enabled) The replication process is by default...
Admin Railbar
Search Index Manager

The plugin:searchindexsearch index manager is a supported plugin that will recreate the If you change a lot of page or after a release, the search index manager should be run to update/rebuild the index....
Undraw Building Websites I78t
Simple Templating markup

Simple templating markup are markups that you can use inside the content of your page: to show information your pages or to show a list of pages Templating offers the following components: ...
Undraw Building Websites I78t
The iterator: Pages iteration made easy

With the fragment iterator, you can loop over a content fragment to create any list of page components such as the list of the last blog or recent pages



Task Runner