CQL Syntax
CQL Basics
CQL, the Card Query Language, is a syntax for finding Decko cards. There's a brief introduction on the CQL card that explains why and where to use it. This card is for folks who want to know how to write CQL.
A CQL statement defines a list of cards by their properties, their relationships to other cards, or both.
Building Blocks
Decko's basic building block is the card. CQL's basic building block is the card query.
Card queries identify a list of one or more cards. In the same way that one card can include other cards, one card query can include other card queries.
To conceptualize this, it may help to think of card queries as a noun phrase in a sentence. For example:
Image cards.
That phrase can be nested inside another noun phrase:
Cards that link to Image cards.
You can extend this idea ad nauseam:
Cards that include cards for editors of cards that link to Image cards...
Those phrases give a general sense of what CQL is querying for, but unlike SQL, CQL is not a plaintext phrase. Instead it is expressed as a compound object built from three simple types.
Note that these simple object types are common to a great variety of languages and platforms, and so CQL queries can be easily translated into any such language.
Card queries are "hashes", or unordered lists of key/value pairs. These hashes use five kinds of keys :
- property - name, content...
- relationship - link to X, included by Y...
- arrangement - sort by name, limit to 10...
- operators - (shortcut for property queries), all the results matching "grantmaker"
- modifier - add "+address" to the end of each result...
Card query values can be any of the three object types:
- simple: for simple matches, card names, etc
- array: for operators
- hash: for nested card queries
A +cheat sheet lists these more concisely, but without the explanation given below here:
Properties
Each card query can describe the following card properties:
- name
- type
- content
- key - canonical name permutation
- id - unique numerical id that remains the same even when the name is changed
Simple values
Each of these properties can be used as the key in a card query. For example, here is a search for the card named Lewis Hoffman.
{"name": "Lewis Hoffman"}
This card happens to have the id 3, so it can also be found this way:
{"id": 3}
Both the name and id uniquely identify cards in Decko, so they each return only one item. This type query however will return a list of several items.
{"type": "Fruit"}
Simple values for content can return lists, too. Note that this query will return only cards where the content is exactly and only "Kiwi".
{"content": "Kiwi"}
To narrow searches, you can combine properties:
{"type":"Fruit", "content": "Kiwi"}
Operators
The above examples represent simple searches where the property equals the value. For example, the type equals "Fruit", or the name equals "Lewis Hoffman". CQL allows more flexible ways to constrain properties, via the following operators:
- match / ~
- gt / >
- lt / <
- eq / = (an explicit form of the default)
- ne / !=
- in
In the previous CQL examples, all the card query values are simple strings. With operators, the values are arrays, as follows:
PROPERTY:[OPERATOR, VALUE]
So, the above example of cards with content equalling Kiwi:
{"content": "Kiwi"}
is actually the short form of the following:
{"content": ["eq", "Kiwi"] }
or
{"content": ["=", "Kiwi"] }
The other operators are more interesting. The "in" operator lets you search on multiple values for a given property. This example finds all the cards whose type is either Releases or Point Releases:
{"type": ["in", "Release", "Point Releases"] }
This one gets all the cards which have "Matt" anywhere in their content, even as part of a word (e.g., in "formatting"):
{"content": ["match", "Matt"] }
Operators Shortcut
There is also a shortcut form for operators.
OPERATOR:VALUE
Note that this form does not specify the property, so CQL has to guess, as follows:
- match - name or content
- all other properties - just content
{"ne": "Kiwi"}
is equivalent to
{"content": ["ne", "Kiwi"] }
And the following example does a search on both name and content:
{"match": "Matt" }
Relationships
Here's where it gets interesting. Cards in Decko are related to other cards in two main ways: references (links and nesting), and compound names). Since these relationships are the source of most structure in Decko, this is the the element of CQL that adds the most depth.
All of these relationships are specified in CQL by nesting card queries. With the exception of certain "plus" relationships, they all take this form:
RELATIONSHIP: CARD_QUERY
References
Cards linking to or including other cards comprise references. (note -- insert links to feature cards inline here)
CQL supports the following reference keys:
- link_to - web links
- linked_to_by
- include - embedded cards
- included_by
- refer_to - web links or embedded cards
- referred_to_by
The simplest reference relationship is the link_to. Here's how you can use CQL to find all the cards linked to the "John Abbe" card:
{"link_to": "John Abbe"}
Notice that in this case our nested card query is just the card name, "John Abbe". This is a short form of the more explicit expression:
{"link_to": {"name":"John Abbe"} }
Written that way, it looks much more like a nested card query: curly brackets inside curly brackets.
Plus cards
Frankly, if CQL ever gets a little mind-flexing, it's in using plus cards. But that's partly because plus cards are such a flexible way of doing things. If you haven't gotten the basic ideas behind compound names, continuing to read here will likely be very confusing. If you get the gist of plus cards, this will all make sense. But don't feel bad if you have to peek again later.
CQL supports two main plus cards keys:
- part - value defines cards that are part of a given plus card
- plus - value defines plus cards of which a given card is part
For each of those, it also supports two side-specific keys:
- left, right
- left_plus, right_plus
Part queries are generally pretty simple. Remember, if it has a part, it is always a plus card! The following finds all the cards that have "website" as a part.
{"part": "website"}
"left" and "right" do similar things, but are a bit narrower. This will get all the plus cards of which "email" is the rightmost part:
{"right": "email"}
Plus queries can get a little more involved. This is because, while a card can only ever have two part cards, it can be plussed to countless other cards. And you often want to refer to them in pairs - the other part and the plus card. For example, you might want to find all of the cards connected to "status" (the other part) where the content of X+status (the plus card) is "closed". So the plus card actually takes two queries. Here's the syntax:
"plus":[OTHER_PART, PLUS_CARD]
...and that example we mentioned:
{"plus":["status",{"content":"closed"}]}
In that one, "status" defines the part, and {"content":"closed"} defines the plus card.
If you give the "plus" key just one card query like a normal card query, then it will treat it as the other part.
"plus":OTHER_PART
For example, this finds all the cards connected to status (regardless of the value of the plus cards):
{"plus":"status"}
Users and roles
CQL lets you query the relationship of users (technically, any card with an account) and roles, using these keys:
- member_of
- member
For example, this finds all of the cards with accounts who have the GC Staff role:
{"member_of":"GC Staff"}
And this finds all of the roles that Lewis Hoffman has:
{"member":"Lewis Hoffman"}
Users and cards
You can also query what cards a user (technically, any card with an account) has created or edited, and who has been editing a card, using these keys:
- edited_by — returns every card this card-with-account (CWA) has ever edited
- editor_of — returns all CWAs who have ever edited this card (was "edited", which is now deprecated)
- created_by — returns cards this CWA created
- creator_of — returns CWA who created this card
For example, this finds all of the cards edited by John Abbe:
{"edited_by": "John Abbe"}
And this finds all of the users who have edited Wiki on Wheels:
{"edited": "Wiki on Wheels"}
Contextual Relationships
Decko uses Virtual cards to create patterns that can be re-used in multiple contexts. The following keys are available. See contextual names for more.
_self, _left, _right, _user, _main, _1, _2, etc.
"Not"
You can use "not" to exclude cards specified by the card query. For example, here are all the User cards with no nerdy nickname card attached yet:
{"type": "User", "not": {"plus": "nerdy nickname"}}
If the card query has multiple keys (properties or relationships), then the negation operates on the entire query. So this:
{"not": {A, B}}
....means "not (A and B)", which is equivalent to "not A OR not B" . For the equivalent of "neither A nor B", you can do the following:
{"not": {"or": {A, B}}}
"Or" and "and"
"Or" expects a series of simple key/value pairs, so if one of the conditions has multiple elements, you need to use the explicit "and", e.g. this:
{"or":
{"type": "Image",
"and": {"type": "User", "plus": "Image"} }
}
In the example "type": "Image" and "and": {...} are two 'pairs' that are the parameters for "or".
Nesting searches
Often you want to specify a search once, then narrow it or arrange it in different ways. This could lead to a multiplication of search cards to maintain, but you can avoid this by using found_by, a key which takes a single Search card as a value, and returns all of the cards that Search returns. Note that the order and view of those cards is lost, and you can only nest once this way. A typical use is *right forms, e.g.:
{"found_by": "_self", "sort": "update"}
Arranging Results
Limits
Limit takes a number, which determines how many cards will be shown at once. This gets passed directly into the SQL.
Ordering
Sort orders the cards, and can take one of four values:
- update — in descending order from most recently edited/updated (this is the default, but we may change, e.g. to respect order of Pointer items found with referred_to_by, so it's best to be explicit)
- create — in ascending order from the card earliest created to the latest
- name — in ascending order, alphabetically (this used to be alpha, which still works)
- content — in ascending order, alphabetically. This works, but only in very simple cases because Basic cards always have HTML and can have Decko markup, both of which do unexpected things to the order. Even Phrase and Plaintext cards can have HTML or Decko markup, but this should be usable on those cardtypes if no one is pasting in HTML. (Numbers are treated as text, and Dates are not stored in a consistent form, so neither is generally usable with sort by content.)
Dir can be used to override the default direction:
- asc — ascending order
- desc — as in descending
Putting these all together, you can tell CQL:
{"type": "User", "sort": "name", "dir": "desc", "limit": "8"}
Modifying Results
Append and prepend let you convert search result cards into new cards. For example, here's a simple way to find all the cards in a deck grouped by their type.
{"type":"Cardtype", "append":"*type", "sort":"name"}
Notice that each card returned is a virtual card, which means that it's not actually stored in the database. There is a RichText card, and a *type, but there is no actual RichText+*type -- it's formed via a search pattern established in *type+*right+*structure. Since most CQL can only return cards in the database, something like this would not work:
{"left":{"type":"Cardtype"}, "right":"*type", "sort":"name"}
Append works because the appending is done after the results are returned.
In addition to virtual cards, you can use append/prepend to return "missing" cards — cards that haven't been created yet. For example, if you're using Decko for an event and you want to invite people to fill in missing contact information, you might do something like this:
{"type": "User", "sort": "name", "append": "how to reach me"}
In general, whenever all the results you want are actual cards, you should not use append/prepend, but rather use left / right.
Returning card count
You can have CQL return the number of cards found instead of the cards themselves. For example, if you wanted to include the number of current users, you'd do this:
{"type": "User", "return": "count"}
By default, Decko provides a card *count you can append to any Search card to instead return a count, like so:
Tips
- "in" doesn't work with "plus", but if you want to search on two plus cards, you can use "plus" for one and "right_plus" for the other.
- When searching on the content of Toggles, search for 0 to find the nos and 1 to find the yeses.
- You can put comments in CQL with:
/* commented text */
Needs
- To date, all operators and sorts are treat numeric card values as text, which means, for example, that 11 comes before 2 (as it would alphabetically). We plan to support better number handling soon.
Also see Related Tickets