Transition from Query to Relation

The Query function and the Relation function share a lot of the concepts (they access the same fields) and also of the syntax, both work on a stack. However, Query is more RPN style and Relation more algebraic. The following text only covers features of Query. But Relation can much more.

Single argument and lowercase

In Query, you must separate instructions with pipe, because every instruction is an argument. In Relation, you use free text. In Query, instructions are uppercase, in Relation, instructions are lowercase.

query	relation
{{query| SELECT film, director| ORDER film}}	{{relation | filter film, director order director}}

We are leaving away the curly parenthesizes and the pipe character for the following.

Explicit print

In Query, the last result is printed at the end. In Relation, you must add explicitly, the print instruction, because you can print at any time in the code. Also, you can print any text with echo or apostrophe notation

query	relation
SELECT film, director	filter film, director print

Filter replaces select and select replaces where

In Query, you define one only selection criteria directly, and you may give a hint. In Relation, you filter first with a hint (for performance) to create the relation and then select the rows on arbitrary criteria. It is easier to define OR-rules. The _namespace is a separate field and replaces FROM.

query	relation
SELECT film, director WHERE director == Godard	filter film, director "godard" select director == "Godard"
SELECT film, director WHERE director == Godard SELECT film, director WHERE director == Truffaut UNION	filter film, director "godard|truffaut" select director == "Godard" or director == "Truffaut"
SELECT film, director FROM films WHERE director == Godard SELECT film, director WHERE director == Truffaut UNION	filter namespace "films", film, director "godard|truffaut" select director == "Godard" or director == "Truffaut" project film, director

Virtual only local

In Query, you can directly choose the fields and apply a selection. On Relation, you first get the table and then apply select and project

query	relation
SELECT film, director FROM Liste des films WHERE director == Godard	virtual "Liste des films" select director == "Godard" project director, film

Current limitation: Relation can not access external pages via swInternalLinkHook nor pass parameters.

Read replaces import

In Query, you can import from a page in tabs or field format. In Relation, you use import to import fields fo a page and read to import CSV or TAB from uploaded files. You can also write CSV or TAB to the cache folder.

Current limitation: Relation can not import TAB from a page.

Algebraic expressions

In Query, only a single level algebraic expression is supported for WHERE and SELECT (and you don't quote strings), and all calculations are in RPN mode. In Relation, you can use quite anywhere algebraic expressions of multiple levels . You don't need COPY, POP, SWAP because you can express it algebraically

query	relation
WHERE director == Godard WHERE film *=* souffle || select director == "Godard" and film regex "souffle"
a b * c +	a * b + c
a b + c *	(a + b) * c
a b .	a . b
a SQRT	sqrt(a)
a 1 5 SUBSTR	substr(a,1,5)
a *~* Foo	urtext(a) regex "foo"
a !0	a !== ""
a r= /Mac(.*)/	a regex "Mac(.*)"
a IN Godard::Truffaut	a regex "^Godard|Truffaut$"

Comparing operators

The numeric operators are the same. There are differences for some string operators which are replaced by regex

query	relation
a =* Foo	a regex "^Foo"
a *= Foo	a regex "Foo$"
a *==* Foo	a regex "Foo"
a ~~ Foo	urtext(a) = "foo"
a ~* Foo	urtext(a) regex "^foo"
a *~ Foo	urtext(a) regex "foo$"
a *~* Foo	urtext(a) regex "foo"
a !0	a !== ""
a r= /Mac(.*)/	a regex "Mac(.*)"
a IN Godard::Truffaut	a regex "^Godard|Truffaut$"

Project replaces group

In Query, you specify by which field you are grouping. In Relation, each field without aggregator is grouping. The names of the aggregators use underscore and not hyphen.

query	relation
GROUP director, film COUNT BY director WHERE film-count > 5	project director, film count select film_count > 5

Extend and update replace calc

In Query, Calc is used both for new and existing fields. Relation makes a differences and reports an error if you try to overwrite an existing field with extend. In Query, all calc are global. In Relation, update can use a where clause.

query	relation
SELECT film, director, year CALC age 2021 year - CALC age age 1 +	filter film, director, year extend age = 2021 - year update age = age + 1

Union and difference on common fields

In Query, except needs a field parameter. In Relation, difference is based on all fields.

Universal join

In Query, there is only a natural join, a left join, a outerjoin and a cross join. Relation has 10 types of join, which are also leftsemi, rightsemi, leftanti, rightanti and theta (any expression). In Query, the join field must be defined (which often means it must be calculated first). In Relation, the join is done on all common fields.

query	relation
SELECT film, director SELECT director, birthdate JOIN director	filter film, director filter director, birthdate join natural
SELECT name, forename, function SELECT name, forename, address CALC fullname name " " . forename . JOIN fullname	filter name, forename, function filter name, forename, address join natural

Use update instead of template

In Query, you apply formats to fields with templates. In Relation, you use directly update. The template instruction is used for complex reporting.

Output

query	relation
OUTPUT HTML	PRINT
HTMLPAGED n	print grid n
LIST	update field = "* ".field
FIELDS	print fields
TAB	print tab
TEXT	print raw