Provide access to album sort order

[Rott-Apple]

While exporting an album in which photos have been manually reordered, the organization seems to be preserved in Catalina but not Mojave.

[RhetTbull]

I don't quite understand the use case? If exporting, then exported images are just a set of files in a directory -- there's no inherent order. Do you mean the order in which files are written to disk? I'm not sure this even matters as the file creation date is preserved when exporting. If you're referring to programmatically accessing the images via an AlbumInfo object, I'll have to look into how the order is stored in the database. osxphotos doesn't currently make any attempt to preserve album order as I don't even pull the sort order from the database. Can you share more details of your use case and what you're trying to do?

Update: @Rott-Apple are you using the command line tool or writing your own export script? Playing around with Photos export I see it offers an export in form "Album-Name-1 of 3.jpg"...the command line tool doesn't support this but if you were writing your own export script, I can see how this information would be useful.

[RhetTbull]

I think I've figured out where the album sort order data is stored in Catalina. The table Z_26ASSETS maps albums to photos. It has 3 columns:

• Z_26ALBUMS: foreign key to ZGENERICALBUM
• Z_34ASSETS: foreign key to ZGENERICASSET
• Z_FOK_34ASSETS: while this appears to be a foreign key I think it's actually a sort order (see discussion below)

If you add photos to an album, the value for Z_FOK_34ASSETS for each photos begins at 2048 then increments by 1024 (2^10) for each photo added, e.g. 2048, 3072, 4096, 5120. In the screenshot below, album with Z_26ALBUMS = 64 (ZGENERICALBUM.Z__PK = 64) has 4 photos added one at a time but not manually sorted:

In the screenshot below, the last photo (Z_34ASSETS = 2) was manually re-ordered to the beginning of the album and has a new value for Z_FOK_34ASSETS of 1024 which places it before the previous first photo with value of 2048.

If you change the sort order manually, the value of Z_FOK_34ASSETS of the photo(s) you manually moved changes so that if ordered by Z_FOK_34ASSETS, the photos will be arranged in the new order.

[Rott-Apple]

I think I've figured out where the album sort order data is stored in Catalina. The table Z_26ASSETS maps albums to photos. It has 3 columns:

* `Z_26ALBUMS`: foreign key to `ZGENERICALBUM`

* `Z_34ASSETS`: foreign key to `ZGENERICASSET`

* `Z_FOK_34ASSETS`: while this appears to be a foreign key I think it's actually a sort order (see discussion below)

If you add photos to an album, the value for Z_FOK_34ASSETS for each photos begins at 2048 then increments by 1024 (2^10) for each photo added, e.g. 2048, 3072, 4096, 5120. In the screenshot below, album with Z_26ALBUMS = 64 (ZGENERICALBUM.Z__PK = 64) has 4 photos added one at a time but not manually sorted:

In the screenshot below, the last photo (Z_34ASSETS = 2) was manually re-ordered to the beginning of the album and has a new value for Z_FOK_34ASSETS of 1024 which places it before the previous first photo with value of 2048.

If you change the sort order manually, the value of Z_FOK_34ASSETS of the photo(s) you manually moved changes so that if ordered by Z_FOK_34ASSETS, the photos will be arranged in the new order.

Thanks, this is most helpful.

[Rott-Apple]

I don't quite understand the use case? If exporting, then exported images are just a set of files in a directory -- there's no inherent order. Do you mean the order in which files are written to disk? I'm not sure this even matters as the file creation date is preserved when exporting. If you're referring to programmatically accessing the images via an AlbumInfo object, I'll have to look into how the order is stored in the database. osxphotos doesn't currently make any attempt to preserve album order as I don't even pull the sort order from the database. Can you share more details of your use case and what you're trying to do?

Update: @Rott-Apple are you using the command line tool or writing your own export script? Playing around with Photos export I see it offers an export in form "Album-Name-1 of 3.jpg"...the command line tool doesn't support this but if you were writing your own export script, I can see how this information would be useful.

OSXPhotos didn't preserve custom ordering so I am trying to write my own export script. The use case is this: After creating an album in Photos app, I can rearrange the photos within that album. I now want to export the album as a folder to Dropbox while preserving the ordering within the exported folder. This would be useful e.g. to share the exported album (folder) with a Windows PC user with the photos in my desired sequence. Hopefully Catalina can now be solved with your previous advice, but where is re-ordering information stored in Mojave and prior? Hugely appreciate your time and effort.

[RhetTbull]

Ah, makes sense. You're correct that osxphotos does not preserve sort order as I hadn't considered this use case. I've figured out where this data is stored in Catalina so I'll modify AlbumInfo.photos() to return photos in album sort order.

I'll need to a take a look at Mojave database again to see if I can figure out where the album order is stored.

What format are you using for photo export name to preserve order? I may be able to also modify the osxphotos template system to enable something like Photos album-name 1 of x.jpg format, for example:
--filename "{album} {album_seq} of {album_total}"

[RhetTbull]

@Rott-Apple Found the sort order for Mojave! It's stored in table RKCustomSortOrder

The highlighted rows in screenshot below show 4 photos in an album with default sort order. Note the orderNumber column uses a similar scheme as in Catalina (start at 1024 and increment by 2^10). containerUuid is the album UUID (RKAlbum.uuid) and objectUuid is the photo UUID (RKVersion.uuid).

In screenshot below, I manually re-sorted the images, moving the second to last image (uuid = 6bxcNnzRQKGnK4uPrCJ9UQ) to the beginning of the album. Note it's orderNumber changed from 3072 to 1536, placing it first in the album.

Now that I can extract the sort order for both Mojave and Catalina, I'll work on adding this to osxphotos.

If you want to use osxphotos yourself in the meantime, you can use the PhotosDB.get_db_connection() to do a custom query -- the snippet below is not complete but should get you pointed in the right direction.

import pathlib
import osxphotos

photosdb = osxphotos.PhotosDB()
conn, cursor = photosdb.get_db_connection()

albums = photosdb.album_info

for album in albums:
    print(f"processing album title: {album.title}")

    results = cursor.execute(
        "SELECT COUNT(*) FROM RKCustomSortOrder WHERE containerUuid = ?", ([album.uuid])
    ).fetchone()
    total = results[0]

    cursor.execute(
        """ SELECT objectUuid, orderNumber 
            FROM RKCustomSortOrder
            WHERE containerUuid = ?
            ORDER BY orderNumber
        """,
        ([album.uuid]),
    )

    sequence = 1
    for row in cursor:
        uuid = row[0]
        photo = photosdb.photos(uuid=[uuid])[0]
        if photo and not photo.ismissing:
            ext = pathlib.Path(photo.path).suffix
            photo.export("/tmp", f"{album.title} {sequence} of {total}{ext}")
            sequence += 1
        else:
            print(f"did not find photo for uuid {uuid}")

conn.close()

[Rott-Apple]

Ah, makes sense. You're correct that osxphotos does not preserve sort order as I hadn't considered this use case. I've figured out where this data is stored in Catalina so I'll modify AlbumInfo.photos() to return photos in album sort order.

I'll need to a take a look at Mojave database again to see if I can figure out where the album order is stored.

What format are you using for photo export name to preserve order? I may be able to also modify the osxphotos template system to enable something like Photos album-name 1 of x.jpg format, for example:
--filename "{album} {album_seq} of {album_total}"

I was thinking of renaming and renumbering the photos and videos in reordered albums as "album_name 00n.filetype", where 00n will increment based on the custom ordering.

[Rott-Apple]

@Rott-Apple Found the sort order for Mojave! It's stored in table RKCustomSortOrder

The highlighted rows in screenshot below show 4 photos in an album with default sort order. Note the orderNumber column uses a similar scheme as in Catalina (start at 1024 and increment by 2^10). containerUuid is the album UUID (RKAlbum.uuid) and objectUuid is the photo UUID (RKVersion.uuid).

In screenshot below, I manually re-sorted the images, moving the last image (uuid = 6bxcNnzRQKGnK4uPrCJ9UQ) to the beginning of the album. Note it's orderNumber changed from 4096 to 1536, placing it first in the album.

Now that I can extract the sort order for both Mojave and Catalina, I'll work on adding this to osxphotos.

If you want to use osxphotos yourself in the meantime, you can use the PhotosDB.get_db_connection() to do a custom query -- the snippet below is not complete but should get you pointed in the right direction.

import pathlib
import osxphotos

photosdb = osxphotos.PhotosDB()
conn, cursor = photosdb.get_db_connection()

albums = photosdb.album_info

for album in albums:
    print(f"processing album title: {album.title}")

    results = cursor.execute(
        "SELECT COUNT(*) FROM RKCustomSortOrder WHERE containerUuid = ?", ([album.uuid])
    ).fetchone()
    total = results[0]

    cursor.execute(
        """ SELECT objectUuid, orderNumber 
            FROM RKCustomSortOrder
            WHERE containerUuid = ?
            ORDER BY orderNumber
        """,
        ([album.uuid]),
    )

    sequence = 1
    for row in cursor:
        uuid = row[0]
        photo = photosdb.photos(uuid=[uuid])[0]
        if photo and not photo.ismissing:
            ext = pathlib.Path(photo.path).suffix
            photo.export("/tmp", f"{album.title} {sequence} of {total}{ext}")
            sequence += 1
        else:
            print(f"did not find photo for uuid {uuid}")

conn.close()

Brilliant! You must practically live inside photos.db to know it so intimately! One typo to fix in your reply (to help others who read it): "orderNumber changed from 4096 to 1536, placing it first in the album" should be "changed from 3072 to 1536". I'll experiment a bit more by rearranging a test album to understand why 3072 changed to 1536 and not 1024, how does renumbering work as I move more and more photos around, etc. Meanwhile, thanks a ton for this brilliant project and such prompt replies.

[RhetTbull]

@Rott-Apple I've fixed AlbumInfo.photos to return photos in the same sort order as Photos in version 0.30.10. This behavior is the same for all versions of the Photos library database.

This should simplify the export script to something like:

import pathlib
import osxphotos

photosdb = osxphotos.PhotosDB()

for album in photosdb.album_info:
    print(f"processing album title: {album.title}")

    sequence = 1
    for photo in album.photos:
        if not photo.ismissing:
            ext = pathlib.Path(photo.path).suffix
            photo.export("/tmp", f"{album.title} {sequence:04}{ext}")
            sequence += 1

[neilpa]

I'll experiment a bit more by rearranging a test album to understand why 3072 changed to 1536 and not 1024,

@Rott-Apple Having implemented user-defined custom sort orders at former $DAY_JOBs I can shed some light. The idea is to leave gaps in the sorting numbers so that re-ordering operations can be applied with a minimal number of updates to other items in the collection. In this case, I suspect what was originally the 3072 photo actually got moved between the 1024 and 2048 sorted photos (1536 = 1024 + (2048-1024)/2) before being moved again to the front of the album.

There are probably some other heuristics as well that attempt to keep the amount of space between items relatively balanced so that continued re-sorting doesn't degenerate quickly. If you always target the same "slot" there's only log2(originalGap) operations before you're forced to do larger re-balances to maintain the intended sort (in this case log2(1024) = 10). That would explain why a few other records got there sort orders shifted as well in the before and after.