Useful Queries

here you find a collection of queries, hopefully useful, organized as question and answer, optionally with some comments about it.

select based on identification

accession where species.sp = 'sp'

this query selects all accessions for which the precise species is not (yet) known.

in bauble 1.0, accessions are always associated to species, so when your accession is only identified at rank genus or family, you have to fake a species. common practice is to add an authorless 'sp' species in the genus and associate the accession to this species.

accession where species.sp like 'sp%'

same as above but more relaxed, selects all objects where the species epithet starts with 'sp'.

if your user inserted species with epithet 'sp.' (including the .), or 'species', or you defined different but unknown species as 'sp.1', 'sp.2'. The like and % syntax is standard SQL.

plant where accession.species.sp = 'sp'

similar as above, but selects plants instead of accessions.

note the difference with the previous query. the field on which the where part is based is one step further: you have to get the accession of the plant before you can get the species of the accession.

accession where species.genus.genus like 'zzz%'

select accessions which have not been identified further than the rank of family.

this is a practice we have chosen at the Quito Botanical Garden. if your accession has not (yet) been identified at rank lower than family, you insert a fictive genus in the family, name it as 'Zzz-<family_epithet without the trailing e>', a 'sp' species in this fictive genus, and associate the accession to this species.

in practice? if you have orchids of which you do not know the genus, you create a 'Zzz-orchidacea' genus, and a 'Zzz-orchidacea sp' species.

accession where species.sp like 'sp%' && ! species.genus.genus like 'zzz%'

select accessions for which the genus is known, but the species is not.

by the way, the operators '&&', '||', '!', can be written also as 'and', 'or', 'not', not case sensitive.

select based on timestamp

accession where _created > |datetime|2015,5,1|

all accessions added to the database after the given timestamp.

you can use this to retrieve for example all that you have been doing the previous day, so you know where you left your work.

also note that you need put the field you are testing at the left-hand-side of the comparison, while on the right hand side of the comparison you must put a constant. you cannot put a constant on the left-hand-side nor a field on the right-hand-side of the comparison.

accession where _created between |datetime|2015,1,1| and |datetime|2016,1,1|

all accessions added during the year 2015.

a timestamp is an instant, not an interval, so to select all that an interval, you have to test the timestamp against the start and the end of the interval, as above. if you want to have all that was added the first of March 2014, you write have to combine an 'after' with a 'before', like in accession where _created between |datetime|2014,3,1| and |datetime|2014,3,2|

select based on notes content.

species where notes.id != 0

all species with at least a note.

accession where plants.notes.id != 0

all accessions which have plants which have notes.

as of bauble-1.0, notes are being (mis)used to hold pictures, so the above query will help you select the accessions which have plant pictures. you can complete the query to obtain all accessions which you might hope to identify: accession where plants.notes.id != 0 && species.sp like 'sp%'.

if you want to limit to a genus, you add the clause, for example if you want to review all masdevallia: accession where plants.notes.id != 0 && species.sp like 'sp%' && species.genus.genus like 'masdev%'

species where notes.category='CITES' OR genus.notes.category='CITES'

all species that override the CITES classification of their family.

for Orchids, this means all species in appendix I.

in bauble 1.0 you cannot use the 'cites' property of a taxon in a selection. with bauble-1.1, you will be able to write the much easier and more explicit query: species where cites = 'I'

accession where species.notes.category='CITES' OR species.genus.notes.category='CITES'

all accessions in the a species that overrides the CITES classification of their family.

genus where (notes.category='CITES' || family.notes.category='CITES') && family.family!='Orchidaceae'

all genera with a cites classification, except orchids.

note that AND/&& has a higher operator precedence than OR/||, so you need use parenthesis to group as desired.

accession where notes.category='factura' && notes.note='03072'

all accessions bought on the same invoice.

This works if you have followed the practice of storing invoice numbers in notes with category 'factura'. Actually the category part you can just leave, the invoice number will most likely be specific enough.

reducing the work load for Bauble

plant where accession.code > '000000' AND accession.code < '001000'

plants in accessions where the accession code is within a range.

this can be useful to prepare adding pictures to plants, so you have a limited amount of plants in your selection, which will make work lighter to Bauble.

generic value matching

if the query does not match the <domain> where <condition> pattern, it is considered as a list of values. Bauble will look for matches in most fields of all database tables. This is a very quick way to locate content in the database, and can at times deliver surprising matches.

stelis

Stelis is a genus in the Orchidaceae family. Asking Bauble to look for stelis will return you also return pseudostelis, pachystelis and apatostelis.

'san josé'

name of a location where your garden might have organized an expedition. the selection will contain the expedition as SourceDetail. all accessions with it as a Source will be connected to the selected object.

it would be best if you give unique names to your expeditions.

be sure to incude quotes if your query is composed by more than one word, otherwise Bauble will select everything which matches any of the words. San José without quotes will return all matching either san or josé, more than 200 items.