Before my vacation one Friday afternoon in July, I got an idea for an app. When exploring the lake in my kayak, the points of interest aren’t registered in Google Maps. If they are by some chance, it’s hard to know where you were precisely you were, so I wanted to tag them on the map with a single tap quickly. With OutSystems, I was able to build an app in about 90 minutes to tag and save various locations and attach photos.
Thinking more about the idea, I realized that I could easily extend this app to other activities like sea kayaking, hiking, or just exploring my neighborhood. I added a table of collections (like tags for blog posts), and a location can belong to multiple collections. Here’s what my data model looks like:
I wanted to add a dialog like the one below to update the collections to which a location belongs. My idea was to have a dialog with a header and then checkboxes for all the collections, indicating if the location is currently included or not.
It’s easy to get the collections that a location belongs to with an aggregate. We can define it like this with the filter
LocationCollection.LocationId=LocationId to only get the current collections for the location we’re editing.
It starts to get tricky when we want to fetch all of the collections and indicate if the location is a member of that collection. If you were going to write it out using SQL, you’d probably write a statement like this:
SELECT collections.id, collections.NAME, sum( CASE WHEN collectionlsocations.locationid=@loc_id THEN 1 ELSE 0 END) FROM collections LEFT JOIN collectionslocations ON collections.id = collectionlsocations.collectionid GROUP BY collections.id
To keep things simple and I made four updates to my aggregate:
- Removed the filter
- Added an attribute called “count” and set it to
If(LocationCollection.LocationId=LocationId, 1, 0)
- Added group by to the collection.id (Id) and collection.name (Name).
- Added sum of “count” (Exists)
Here’s how the final modal looks:
Using OutSystems, it took me less time to implement this feature than to summarize how to pull the data because I didn’t need to write that gnarly SQL statement up above and could leverage standard components.