4. Business Users Requests Implementation

Business Users Requests Implementation

SQL to handle business logics

Select all images from the label's children

This is for user to download a branch of images under a sub-category. For example, if a user want to download all the images under the parent label "Drink" with our data modeling, the user will be able also download all images for all the labels recursively under "Drink".

SELECT full_hadoop_path, image_thumbnail_object_key,label_name
FROM images
WHERE label_name IN (SELECT unnest(l.children)
FROM labels l
WHERE l.label_name = 'Drink'); 

---

Insert New Label with given parent Label

If user is looking for a label that is not already in Deep Image Hub(DIH), the user can make the request to DIH by suppling an immediate parent label of the requesting label. Then the following Common Table Expressions(CTEs) SQL statement will recursively insert the new label into the parent label and all the way up to the final parent label "Entity".

The following example is for user requesting to add new label 'LaCroix_Sparkling_Water_Lemon' by supplying the immediate parent label 'Soft_drink'.

INSERT INTO labels (label_name, parent_name, children, image_count, updated_date) 
VALUES
('LaCroix_Sparkling_Water_Lemon', 'Soft_drink', NULL, 0, (SELECT NOW()));

 WITH RECURSIVE labeltree AS ( 
 SELECT parent_name
 FROM labels 
 WHERE label_name = 'LaCroix_Sparkling_Water_Lemon'
 UNION ALL
 SELECT l.parent_name 
 FROM labels l 
 INNER JOIN labeltree ltree 
 ON ltree.parent_name = l.label_name 
 WHERE l.parent_name IS NOT NULL) 
	
 UPDATE labels 
 SET children = array_append(children, 'LaCroix_Sparkling_Water_Lemon') 
 FROM labeltree	ltree										  
 WHERE label_name = ltree.parent_name;

---

If there are not enough images for the label, insert user request into requesting_label_watchlist

The following SQL is to handle the use case when user triggered a computer vision model training request, but the image counts for particular labels do not meet the threshold (100 for now), then the label will be added into the requesting_label_watchlist table.

The current process also handle multi-users' requests on the same label.

INSERT INTO requesting_label_watchlist (label_name, user_ids,last_requested_userid, new_requested_date ) 
VALUES
( 'LaCroix_Sparkling_Water_Lemon',ARRAY[2], 2, (SELECT NOW()) )
ON CONFLICT (label_name)  
DO
UPDATE
SET user_ids = array_append(requesting_label_watchlist.user_ids, 2),last_requested_userid = 2
WHERE requesting_label_watchlist.label_name = 'LaCroix_Sparkling_Water_Lemon';