immich/mobile/lib/infrastructure/entities/merged_asset.drift

import 'remote_asset.entity.dart';
import 'stack.entity.dart';
import 'local_asset.entity.dart';
import 'local_album.entity.dart';
import 'local_album_asset.entity.dart';

mergedAsset: 
SELECT
	rae.id as remote_id,
	(SELECT lae.id FROM local_asset_entity lae WHERE lae.checksum = rae.checksum LIMIT 1) as local_id,
	rae.name,
	rae."type",
	rae.created_at as created_at,
	rae.updated_at,
	rae.width,
	rae.height,
	rae.duration_in_seconds,
	rae.is_favorite,
	rae.thumb_hash,
	rae.checksum,
	rae.owner_id,
	rae.live_photo_video_id,
	0 as orientation,
	rae.stack_id
FROM
	remote_asset_entity rae
LEFT JOIN
	stack_entity se ON rae.stack_id = se.id
WHERE
	rae.deleted_at IS NULL
	AND rae.visibility = 0 -- timeline visibility
	AND rae.owner_id in ?
	AND (
		rae.stack_id IS NULL
		OR rae.id = se.primary_asset_id
	)

UNION ALL

SELECT
	NULL as remote_id,
	lae.id as local_id,
	lae.name,
	lae."type",
	lae.created_at as created_at,
	lae.updated_at,
	lae.width,
	lae.height,
	lae.duration_in_seconds,
	lae.is_favorite,
	NULL as thumb_hash,
	lae.checksum,
	NULL as owner_id,
	NULL as live_photo_video_id,
	lae.orientation,
	NULL as stack_id
FROM
	local_asset_entity lae
WHERE NOT EXISTS (
	SELECT 1 FROM remote_asset_entity rae WHERE rae.checksum = lae.checksum
)
AND EXISTS (
	SELECT 1 FROM local_album_asset_entity laa
	INNER JOIN local_album_entity la on laa.album_id = la.id
	WHERE laa.asset_id = lae.id AND la.backup_selection = 0 -- selected
)
ORDER BY created_at DESC
LIMIT $limit;

mergedBucket(:group_by AS INTEGER):
SELECT
	COUNT(*) as asset_count,
	 CASE
        WHEN :group_by = 0 THEN STRFTIME('%Y-%m-%d', created_at, 'localtime') -- day
        WHEN :group_by = 1 THEN STRFTIME('%Y-%m', created_at, 'localtime') -- month
    END AS bucket_date
FROM
(
	SELECT
		rae.created_at
	FROM
		remote_asset_entity rae
	LEFT JOIN
        stack_entity se ON rae.stack_id = se.id
	WHERE
		rae.deleted_at IS NULL
		AND rae.visibility = 0 -- timeline visibility
		AND rae.owner_id in ?
		AND (
			rae.stack_id IS NULL
			OR rae.id = se.primary_asset_id
		)
	UNION ALL
	SELECT
		lae.created_at
	FROM
		local_asset_entity lae
	LEFT JOIN
		remote_asset_entity rae ON rae.checksum = lae.checksum
	LEFT JOIN
		local_album_asset_entity laa ON laa.asset_id = lae.id
	LEFT JOIN
		local_album_entity la ON la.id = laa.album_id
	WHERE
		rae.id IS NULL
		AND la.backup_selection = 0 -- selected
)
GROUP BY bucket_date
ORDER BY bucket_date DESC;