Querying
(for Filters and Conditional Formats)
iThoughts uses a powerful query language (called SQL) when filtering or defining conditional formats. SQL is a standard ‘English like’ language used in the database world (Google for ‘sqlite where clause’)
A query is a ‘description’ of a set of attributes that you’re interested in.
Some simple example queries might be:
progress=100
shape=2
priority=1 and icon like '%smiley%'
…which I hope are reasonably self-explanatory?
When structuring your query, you need to know what values you can query for (and their type.) The following table gives you the field names/types.
| Field Name | Type | Description |
|---|---|---|
| created | integer | The date/time (unix) when the topic was created |
| modified | integer | The date/time (unix) when the topic was last modified |
| text | text | The topic text as displayed (NOT the markdown) |
| note | text | The topic note as displayed (NOT the markdown) |
| link | text | The link attached to a topic |
| shape | integer |
|
| color | text | The RGB color of the topic (eg FF0000 for red) |
| icons | text | Comma delimited list of icon names |
| priority | integer | Number from 1-5 (or -1 if not set) |
| progress | integer | Progress from 0-100 (or -1 if not set) |
| start | integer | The START date/time (unix) |
| due | integer | The DUE date/time (unix) |
| effort | real | The effort (in hours) |
| cost | real | The cost |
| resources | text | Comma delimited list of resource names |
Querying for Text (text, notes, links, shapes, colors etc.)
When querying for textual values you should use LIKE. Where you want a wildcard match you can use %.
For example if you want to match on all topics that have a smiley then you would use the following:
icons like '%smile%'
The % matches on any characters. So in the above, any icons with smile in their name will be matched.
[ADVANCED] Text Matching
Whilst the technique above (using LIKE) is probably ok for most and extremely fast - there are times when you want more control of the textual searching. For those times you can use regular expressions. The example below will find all topics that end with the text ‘sausages’
text REGEXP 'sausages$'
NB: REGEXP matches take a little more time to process so you might find a slight delay on larger maps.
Querying for dates
Querying for dates is a little more complex - but very powerful. The following example will pull out all topics modified yesterday:
DATE(modified,'unixepoch','localtime') = DATE('now','-1 day','localtime')
DATE(modified,'unixepoch','localtime') extracts the modified date and adjusts it for daylight saving time. The ‘unixepoch’ tells the system what format the modified date is stored in (i.e. the number of seconds since 1970.)
DATE('now','-1 day','localtime') calculates the date for yesterday (now - 1day)
The two dates are then compared for equality.
Compound Queries
Individual queries can be strung together with AND or OR and NOT and nested with brackets. For example the following will pull out all topics DUE anytime before now+7days AND where their progress is not 100 (i.e. complete)
DATE(due,'unixepoch','localtime') <= DATE('now','+7 day','localtime') and progress != 100
Icon Names
Each icon has a fixed ‘name’ (in English.) To determine the name:
On Mac/Windows, hover your mouse over the icon in the icon picker (a tooltip will tell you the name)
On iOS long press an icon in the icon picker (the ID will be in square brackets)
Colors
Colors are stored as RGB values.
On Mac/Windows, hover your mouse over the color in the color picker (a tooltip will tell you the value)
On iOS tap on the Inspector toolbar button then on the Color tab and then on More (bottom right corner.) The RGB color will be displayed in the top right corner.
Other Stuff
Searches of the text and notes fields will search the text as it appears on screen NOT the underlying Markdown formatted text.
Querying is quite a complex beastie - but there is plenty on the internet regarding SQLite WHERE clauses - and you can alway email me…