-
Notifications
You must be signed in to change notification settings - Fork 34
Useful Queries
here you find a collection of queries, hopefully useful, organized as question and answer, optionally with some comments about it.
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.
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.
similar as above, but selects plants instead of accessions.
note the difference with the previous query. the field on which the
wherepart is based is one step further: you have to get the accession of the plant before you can get the species of the accession.
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.
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.
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.
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|
all species with at least a note.
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%'
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'
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 thanOR/||, so you need use parenthesis to group as desired.
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
categorypart you can just leave, the invoice number will most likely be specific enough.
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.
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 is a genus in the Orchidaceae family. Asking Bauble to look for stelis will return you also return pseudostelis, pachystelis and apatostelis.
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 eithersanorjosé, more than 200 items.