Get Family Data (function)

build-family-json-object.sql

DROP FUNCTION IF EXISTS get_family_data;

CREATE OR REPLACE FUNCTION get_family_data(familyId integer)
RETURNS jsonb AS $$
    WITH counts AS (
        SELECT
            f.id AS family_id,
            COUNT(DISTINCT i.id) AS icons_count,
            COUNT(DISTINCT il.id) AS illustrations_count,
            COUNT(DISTINCT s.id) AS sets_count
        FROM families f
        LEFT JOIN sets s ON s.family_id = f.id
        LEFT JOIN icons i ON i.set_id = s.id
        LEFT JOIN illustrations il ON il.set_id = s.id
        WHERE f.id = familyId
        GROUP BY f.id
    )
	, asset_images AS (
        SELECT
            img.id,
            url,
            access,
            visibility,
            image_type_id,
            it.value as image_type,
            it.label as image_type_label,
            entity_type,
            entity_id,
            file_type
        FROM images img 
        LEFT JOIN image_types it ON it.id = img.image_type_id
        WHERE img.entity_type in ('icon', 'illustration') 
        AND img.entity_id in (
            select id from icons where icons.set_id in (
                select id from sets where sets.family_id = familyId
            )
        )
    )
	, set_images AS (
        SELECT
            img.id,
            url,
            access,
            visibility,
            image_type_id,
            it.value as image_type,
            it.label as image_type_label,
            entity_type,
            entity_id,
            file_type
        FROM images img 
        LEFT JOIN image_types it ON it.id = img.image_type_id
        WHERE img.entity_type in ('set') 
        AND img.entity_id in (
            select id from sets where sets.family_id = familyId
        )
    )
	, icons AS (
		 SELECT
           ic.id,
			ic.name,
			ic.unique_id,
			ic.style_id, 
			ic.
        FROM images img 
        LEFT JOIN image_types it ON it.id = img.image_type_id
        WHERE img.entity_type in ('set') 
        AND img.entity_id in (
            select id from sets where sets.family_id = familyId
        )
	)
    SELECT jsonb_build_object(
        'id', f.id,
        'name', f.name,
        'price', f.price,
        'license_id', f.license_id,
        'sort', f.sort,
        'is_active', f.is_active,
        'created_at', f.created_at,
        'updated_at', f.updated_at,
        'description', f.description,
        'user_id', f.user_id,
        'unique_id', f.unique_id,
        'sets', jsonb_agg(
            jsonb_build_object(
                'id', s.id,
                'name', s.name,
                'type', (
                    select value from product_types where id = s.type_id
                ),
                'images', (
                    SELECT jsonb_agg(
                        jsonb_build_object(
                            'id', img.id,
                            'url', img.url,
                            'image_type_id', img.image_type_id,
                            'image_type', img.image_type,
                            'image_type_label', img.image_type_label,
                            'file_type', img.file_type
                        )
                    )
                    FROM set_images img
                    WHERE img.entity_id = s.id
                )
            )
         ),
        'illustrations_count', counts.illustrations_count,
        'icons_count', counts.icons_count,
        'sets_count', counts.sets_count,
        'total_assets', counts.illustrations_count + counts.icons_count,
        'images', (
            SELECT json_agg(jsonb_build_object(
                'url', img.url,
                'image_type_id', img.image_type_id,
                'image_type', it.value,
                'image_type_label', it.label,
                'file_type', img.file_type,
                'access', img.access
            )) 
            FROM images img 
            LEFT JOIN image_types it ON it.id = img.image_type_id
            WHERE img.entity_id = f.id 
            AND img.entity_type = 'family'
        ),
        'license', (
            SELECT jsonb_build_object(
                'id', l.id,
                'name', l.name,
                'text', l.text,
                'is_active', l.is_active,
                'updated_at', l.updated_at,
                'created_at', l.created_at
            ) FROM licenses l WHERE l.id = f.license_id
        ),
        'user', (
            SELECT jsonb_build_object(
                'id', u.id,                
                'username', u.username,
                'first_name', u.first_name,
                'last_name', u.last_name,
                'image_url', (
                    SELECT url FROM images WHERE entity_id = u.id AND entity_type = 'user' LIMIT 1
                ),
                'is_favorite', EXISTS (
                    SELECT 1 FROM favorites WHERE entity_id = u.id AND entity_type = 'user' AND user_id = f.user_id
                ),
                'icons_count', (
                    SELECT COUNT(id) FROM icons WHERE icons.user_id = u.id
                ),
                'illustrations_count', (
                    SELECT COUNT(id) FROM illustrations WHERE illustrations.user_id = u.id
                )
            ) FROM users u WHERE u.id = f.user_id
        ),
        'is_favorite', EXISTS (
            SELECT 1 FROM favorites WHERE entity_id = f.id AND entity_type = 'family' AND user_id = f.user_id
        ),
        'tags',  (
            SELECT CASE
                WHEN EXISTS (
                    SELECT 1
                    FROM entity_to_tags et
                    left join tags t on t.id = et.tag_id
                    WHERE et.entity_id = f.id and et.entity_type = 'families'
                ) THEN (
                    SELECT jsonb_agg(
                        distinct(t.name)
                    )
                    FROM entity_to_tags et
                    left join tags t on t.id = et.tag_id
                    WHERE et.entity_id = f.id and et.entity_type = 'families'
                )
                ELSE '[]'::jsonb
            END
        )
    )
    FROM families f
    LEFT JOIN sets s ON s.family_id = f.id
    JOIN Counts counts on f.id = counts.family_id
    WHERE f.id = familyId
    GROUP BY f.id, f.name, f.price, f.license_id, f.team_id, f.sort, f.is_active,
        f.created_at, f.updated_at, f.description, f.user_id, f.unique_id, 
        counts.icons_count, counts.illustrations_count, counts.sets_count;  
$$ LANGUAGE SQL;