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.

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', but if you prefer the text version, note that they MUST be in upper case.

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 > |datetime|2015,1,1| && _created < |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 > |datetime|2014,3,1| && _created < |datetime|2014,3,2|

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.

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.

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.