Specify 7 Query Builder

Query Builder is an interface for creating advanced queries against the database. It is part of the collection management software, Specify 7.

I worked on the front end, including the following features:

• View results in a table (with infinite scrolling)
• View results record-by-record in a form
• Add "OR" conditions to fields
• Run a geospatial query (specify coordinates via dragging pins on a map)
• Visualize query results using an interactive map
• Integrates with the permission system. Hides data from tables you do not have "read" access to
• WCAG 2.1 (accessibility standard) compliant user interface. Supports screen readers, keyboard navigation, and dark mode

Screenshots

Presentation from iDigBio Digital Data Conference 2022

Video overview of the query builder, accessibility, and other related features

Spatial Search

The query builder also supports spatial search. You have an ability to constrain search to a region you drawn on the map or plot query results on an interactive map.

Online demo

You can try out the live version at sp7demofish.specifycloud.org. The username and password are sp7demofish. When prompted to select a collection, choose any option. See usage instructions in the video above.

Guided demo

A video recording of a zoom show-and-tell session is available. It covers new features in the Specify 7.7 release and describes Query Builder enhancements.

An overview of all new features in Specify 7.7 release.

Technologies used

• JavaScript
• TypeScript
• React
• Tailwind CSS
• Leaflet (library for interactive maps)

Things learned

During the development of the query builder, my assumption was that the more validation on data and control over user I put the better. My reasoning was that requiring all inputs to comply with strict requirements would prevent many error conditions and give users an early indicator of something going wrong.

However, I went overboard on this to the point were some valid use cases were disallowed by the validation system.

For example, in our software, you can change field length and format requirements for a given field. The query builder interface was looking at these requirements and interesting search query to only values that match these requirements. A thing I haven't considered is that the requirements could be changed, while the data remains the same. This leads to users wanting to query data that doesn't match the current format and not being allowed to do so because of the rigid validation system.

Lifting some validation restrictions while turning others from errors into warnings resolved these issues.