mirror of
https://github.com/immich-app/immich.git
synced 2025-08-11 09:16:31 -04:00
119 lines
2.9 KiB
Plaintext
119 lines
2.9 KiB
Plaintext
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 :user_ids
|
|
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 rae.owner_id IN :user_ids
|
|
)
|
|
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
|
|
)
|
|
AND NOT 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 = 2 -- excluded
|
|
)
|
|
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 :user_ids
|
|
AND (
|
|
rae.stack_id IS NULL
|
|
OR rae.id = se.primary_asset_id
|
|
)
|
|
UNION ALL
|
|
SELECT
|
|
lae.created_at
|
|
FROM
|
|
local_asset_entity lae
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM remote_asset_entity rae WHERE rae.checksum = lae.checksum AND rae.owner_id IN :user_ids
|
|
)
|
|
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
|
|
)
|
|
AND NOT 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 = 2 -- excluded
|
|
)
|
|
)
|
|
GROUP BY bucket_date
|
|
ORDER BY bucket_date DESC;
|