Looking up array of values

App Building
, , ,
1

I have a sql query that returns an array of values for a column.

Select Trades from SubcontractorTrades

returns [1,4]

How can I cross-reference a table for the name of these values?

Ex.
1 - Roof
2 - Siding
3 - Gutter
4 - Fence

2

Hey @nroeder!

Would something like this work for you?

SELECT

(SELECT name FROM Trades WHERE id = t.subcontractor_column_id) AS TradeName

FROM SubcontractorTrades t

Where Trades is your database table that contains the 1 - Roof mapping (e.g. id = 1 and name_column = Roof). You could also write a JOIN to connect the two tables.

Or if your 1 - Roof mapping isn't stored in a SQL table, you could do something like this:

const mapping = {1: 'Roof', 2: 'Siding', 3: 'Gutter', 4: 'Fence'}
return formatDataAsArray(data).map(row => ({...row, id: mapping[row.id]}))

image
image1850×1274 232 KB

Let me know if you have any questions at all!

3

So I get this using the above.

image
image1121×555 53.3 KB

I have this working but I still have a question about the underlying data architecture.

select *, CONCAT(ty.TradeName, ' - ', ty.TradeNameType) as FullTrade
from SubcontractorTrades as t
OUTER APPLY STRING_SPLIT(t.SubcontractorTradeTypeId, ',') s
left join SubcontractorTradeType as ty on s.value = ty.SubcontractorTradeTypeId
Where SubcontractorId = {{selectedSubcontractor.value}}

So what is the best way to handle tag data on a UID, if those tags are going to change semi-frequently. Also would like a table view that showed all those tags in a single column for a UID and the ability to filter by tags.

I think the data should be structured like this but based on the criteria above would this be the best format for the data?
Ex. Subcontract-Table SubcontractorId SubcontractorName, Trades-Table, TradeId, SubcontractorId, TradeName, TradeCategory

4

Based on what I understand from your current setup, it seems like you want to handle tags associated with a unique identifier (UID), and you also want the ability to easily filter and display the tags in a single column for each UID. Based on that, a possible data structure could be as follows:

SubcontractorTable:

  • SubcontractorId (unique identifier for each subcontractor)
  • SubcontractorName

TradesTable:

  • TradeId (unique identifier for each trade)
  • SubcontractorId (foreign key referencing the SubcontractorTable)
  • TradeName
  • TradeCategory

TagsTable:

  • TagId (unique identifier for each tag)
  • TradeId (foreign key referencing the TradesTable)
  • TagName (the actual tag value)

This structure allows you to associate multiple tags with each trade, and trades are linked to the corresponding subcontractor through the SubcontractorId foreign key.

To retrieve all the tags associated with a particular UID, you can join the tables and use a WHERE clause to filter by the SubcontractorId. Here's an example query:

SELECT t.TradeName, t.TradeCategory, GROUP_CONCAT(tags.TagName) AS Tags 
FROM TradesTable AS t 
JOIN TagsTable AS tags 
ON t.TradeId = tags.TradeId 
WHERE t.SubcontractorId = {{selectedSubcontractor.value}} 
GROUP BY t.TradeId;

This query retrieves the trade name, trade category, and a concatenated list of tags for each trade associated with the specified subcontractor. The GROUP_CONCAT function is used to combine multiple tags into a single column.

With this data structure, you can easily filter trades by tags using appropriate JOIN and WHERE conditions in your queries.

Keep in mind that this is just one possible approach, and the best data structure depends on the specific needs of your application and the queries you'll be executing!

1 Like
5

So if I want to say Remove 1 Tag and add 2 tags What would be the best method to execute this?

6

Are you looking for the SQL? :slight_smile:

If so,

GET tags:

SELECT TagId, TagName FROM TagsTable WHERE TradeId = {{tradeId}};

DELETE tags:

DELETE FROM TagsTable WHERE TagId = {{tagIdToDelete}};

ADD tags:

INSERT INTO TagsTable (TradeId, TagName) VALUES ({{tradeId}}, 'Tag1'), ({{tradeId}}, 'Tag2');

Are you ideally using the GUI mode or SQL mode to write your delete/insert queries?

7

So this won't work. So if you had multiselect dropdown for tags. The options being (Tag1, Tag2, Tag3, Tag4). On the initial insert the execution is easy, you select Tag1 and Tag3 and execute the insert query. However if you load the subcontractor and show which tags he has Tag1 and Tag3 and then decided you want to remove Tag3 and add Tag2 this is where the problem occurs. You either have to delete all tags and then insert both Tag1 and Tag2 or you have to somehow check which tags were removed, then delete only those tags and then insert the new tags. Ugo suggested putting all tags in a single cell separated by spaces. I'm currently doing that unless you have a better suggestion.