Newbie - .length help for Statistic component

App Building
1

Hello!

I am completely new to Retool and Java all together so I appreciate any help I can get. I have searched through the forums and see to have come across some possible fixes, but with my limited knowledge of Java I'm not sure how to make it work for my use-case.

I am attempting to use the statistic component to count the "Active" equipment of different types. I have been successful with creating a separate query for each statistic, but it seems incredibly inefficient. It is currently working when I create a query that looks like:

select * from "equip_mgmt_sys_v2" where ("status" ilike 'Active' and "equipment_type" ilike 'Vehicle');

and then a statistic component that finds its primary value through:
{{ equip_data_active_vehicle.data.unit.length }}

Again this is working but I feel like creating 30 different queries for 30 different types of equipment is not efficient. Any suggestions are welcome.

Thank you!

2

If you just need the counts, why not a SQL query like this?

select equipment_type, count(*) 
from equip_mgmt_sys_v2 
where status like 'Active' 
group by equipment_type;
2 Likes
3

How simple! I knew the 30 queries was silly. For the primary value in my statistic component, do I need to directly reference the row number or is there a way I can reference the "vehicle" part instead?

{{equip_data_active.data.count['13']}}

4

@troth16 the responses from SQL queries are a bit inconvenient to work with, so you have to transform them to make access easier.

Maybe something like {{_.groupBy(formatDataAsArray(equip_data_active.data))['Vehicle'][0].count}}. You'll have to try a few things to see what's best.

5

I have been trying to work the line of code you provided for a couple hours now, but to no avail. The only successful output I can get is if I reference it by row number. Any reference to the data provided in the row such as "Vehicle" returns a null or undefined.

I appreciate your help with this and apologize for the amateur hour. Do you have anything you recommend I start with learning for writing these SQL queries and better understanding the language going forward?

6

Hi @troth16

Thanks for checking in!

Here's a very similar example:

{{_.find(formatDataAsArray(sqlQuery.data), {city: 'GRANITE BAY'}).count}}

I'm filtering by city, but it sounds like yours would be filtered by the specific vehicle

If that doesn't help or isn't quite what you're looking for, can we see the data output in the left panel of your app editor (expanded out to see the data structure)? It should look something like this:

Our new user documentation includes a link to an external SQL guide: https://docs.retool.com/docs/retool-fundamentals We also have some SQL query information in our docs here: https://docs.retool.com/docs/sql-queries

7

The Find function worked. Thank you all! I'll start reading the materials you provided and hopefully be able to return the favor eventually.

8

Awesome! :raised_hands: