Photo Archive

Shared by Jeff Richmond, The Well Community Church
3 years ago

12.0 Operations, General Intermediate

Background

Our Content & Media Coordinator asked me for suggestions for software that provides a searchable photography archive that would also allow her to give her volunteer photographers access to upload their own photos. I found several options that were either really expensive, poorly built or just not really meant for archiving photos. I eventually realized that a solution that met nearly all of her needs could be built fairly easily using Rock. This recipe is what I came up with.

Summary

The photo archive consists of collections of photos that each have a date, location, photographer and topics associated with them, all of which is searchable. Each collection also has a date and location that is used for any photos with an empty date or location.

The admin portion of the archive has pages for searching, uploading and managing photos, as well as pages for managing collections, locations, topics, photographers, and security. The public side includes pages for viewing collections and viewing, uploading and managing photos.

All public and admin pages are locked down by security roles. I've included roles for restricting viewing, uploading, editing, deleting and all admin tasks.

For this recipe you will be creating the following new entities…

4 × Defined Types
1 × File Type
1 × Global Attribute
5 × Security Roles
1 × Group
3 × Workflows
9 × Admin Pages
3 × Public Pages

Prerequisites

The Workflow Extensions plugin by BEMA Software Services
The File Metadata Shortcodes recipe (at least the filesize shortcode)
The Content Pagination Shortcode recipe
Also uses Photoswipe.js photo gallery (files included below)

How-To

There are a lot of pieces that go into this photo archive, many of which depend on each other to work. For the most part these directions have been arranged so that the required entities are created before the entities that reference them. There are only a couple potential chicken/egg situations (like a child page referenced by a block on the parent page). I've made notes of those below.

I included a SQL file called gather-entity-ids.sql that should automatically give you the IDs of any entities you will need to reference. The query makes some assumptions about your entity names, but if you stick to the naming conventions specified below, you shouldn't need to make any adjustments to the query.

Any property, attribute, setting, option, field, etc. that has been omitted from these instructions can be left with the default value.

Security Roles

Create security roles to control access to different features of the photo archive.

Our role names begin with WSR - ("Well Security Role") to indicate which ones we created vs system or plugin created roles, but feel free to name your roles however you want. Just make sure they all have a consistent prefix that is unique to the photo archive (like WSR - Photo Archive -) since we'll be using the prefix to load the correct roles on the Photo Archive Permissions page.
- Admin
  - Name: WSR - Photo Archive - Admin
  - Desc: Staff members with full access to administrate all aspects of the photo archive, including security
  - Security: Admin role Allow Manage Members
- Delete
  - Name: WSR - Photo Archive - Delete
  - Desc: Users with the ability to delete photos
  - Security: Admin role Allow Manage Members
- Editor
  - Name: WSR - Photo Archive - Editor
  - Desc: Users with the ability to upload photos and edit details of existing photos
  - Security: Admin role Allow Manage Members
- Uploader
  - Name: WSR - Photo Archive - Uploader
  - Desc: Users with the ability to upload photos
  - Security: Admin role Allow Manage Members
- Viewer
  - Name: WSR - Photo Archive - Viewer
  - Desc: Users with the ability to view the photo archive
  - Security: Admin role Allow Manage Members
We didn't feel the need to restrict individual collections, so each type of permission is all or nothing.

We also wanted our entire staff to be able to view the photos, so every place we gave permission to the Photo Archive - Viewer role, we also added RSR - Staff Workers. If you have different requirements, you can just leave the staff role out.
File Type

Create a new file type for the photos that will be uploaded.
- Name: Photo Archive Image
- Desc: Images for the photo archive
- Icon CSS Class: fas fa-images
- Storage Type: Your choice. We're using Azure Blob Storage
- Security: Admin, Editor & Uploader roles Allow Edit
Global Attribute

Create a new global attribute that will store the default image that will be used as a thumbnail for empty collections.
- Name: Photo Archive Placeholder Image
- Key: PhotoArchivePlaceholder
- Field Type: Image
- File Type: Photo Archive Image
- Default Value: Upload the image you want to use. An example file (no-file.svg) is included in the attached zip file if you'd like.
Photographers Group

Create a group for managing the list of photographers that can be selected for each photo. The photographers group is not a security role. Members do not receive any special permissions.
- Name: Photographers
- Public: False
- Description: People who can be selected as photographers for the photo archive
- Group Type: General (doesn't really matter)
- Security: Admin role Allow Manage Members
Defined Types

Create defined types for storing all of the data for the photo archive.

I tried many other entity types before landing on Defined Types. It seemed like the simplest and most streamlined solution for what I needed. This is definitely open for debate though.
- Photo Archive Locations
  - Desc: Locations for photo archive collections and photos
  - Security: Admin role Allow Edit
- Photo Archive Topics
  - Desc: Topics for tracking photos in the photo archive
  - Security: Admin role Allow Edit
- Photo Archive Collections
  - Desc: Events or categories for grouping photos in the photo archive
  - Attributes:
    - Date
      - Key: Date
      - Show in Grid: Yes
      - Field Type: Date
    - Location
      - Key: Date
      - Show in Grid: Yes
      - Field Type: Defined Value
      - Defined Type: Photo Archive Locations
      - Enhance For Long Lists: Yes
      - Allow Adding New Values: Yes
  - Security: Admin role Allow Edit
- Photo Archive Photos
  - Desc: Photographs for the photo archive
  - Attributes:
    - Collection
      - Key: Collection
      - Required: Yes
      - Show in Grid: Yes
      - Field Type: Defined Value
      - Defined Type: Photo Archive Collections
      - Enhance For Long Lists: Yes
    - Photo
      - Key: Photo
      - Required: Yes
      - Show in Grid: Yes
      - Field Type: Image
      - File Type: Photo Archive Image
    - Photographer
      - Key: Photographer
      - Show in Grid: Yes
      - Field Type: Person
      - Enable Self Selection: Yes
    - Date
      - Key: Date
      - Show in Grid: Yes
      - Field Type: Date
    - Location
      - Key: Location
      - Show in Grid: Yes
      - Field Type: Defined Value
      - Defined Type: Photo Archive Locations
      - Enhance For Long Lists: Yes
      - Allow Adding New Values: Yes
    - Topics
      - Key: Topics
      - Show in Grid: Yes
      - Field Type: Defined Value
      - Defined Type: Photo Archive Topics
      - Allow Multiple Values: Yes
      - Enhance For Long Lists: Yes
      - Allow Adding New Values: Yes
  - Security: Admin, Editor & Uploader roles Allow Edit
Photoswipe.js

The photo archive uses Photoswipe.js for viewing larger photos and navigating between them. All of the necessary files are included in the photoswipe folder in the attached zip file.

It's completely up to you to decide where you want to upload the files. Photoswipe is used for both the admin and public side, but since we were already using it for our public website, all of our PhotoSwipe files are located in our public theme's assets folder. You'll just need to make sure to update the path references in the Lava to match your own folder structure.
Lava Files

I ended up with a several external Lava files that are included in a few places. They don't have to be external files. It just feels cleaner to me and usually works better for my personal workflow. Depending on your preference, you can either upload the files to your server or just copy the content to replace the Lava include statements. I'll make a note of where the includes happen below. The Lava files can be found in the lava folder of the attached zip file.

There are some IDs, file paths, etc at the top of most of the files that you'll need to change. The workflow IDs in photo-archive-photo-list.lava: will have to wait until you create the workflows in the next step, but the rest can be updated now if you'd like.
- photo-archive-collection-list.lava: (Optional) Change the pageSize to limit the number of collections shown on the page at once
- photo-archive-orphans-button.lava: Photos Defined Type ID & Photos Defined Type > Collections Attribute ID
- photo-archive-photo-list.lava:
  - Workflow Type IDs
  - Photoswipe.js file paths. There are a total of five paths to update (two stylesheets and three JS files.)
  - (Optional) Change the pageSize to limit the number of collections shown on the page at once
- photo-archive-photo-stats.lava: Photos Defined Type ID & Photos Defined Type > Photo File Attribute ID
- photo-archive-security-role-list.lava: Change the role prefix to match your security roles naming convention
- photo-archive-upload-photos.lava: No changes necessary
Workflow Types

There are three workflow types that are used for managing photos. Use the JSON files in the workflows folder in the attached zip file to import them into your Rock instance.

Once the workflow types are imported, there are several changes you'll need to make to them…

Upload New Photos
- Workflow:
  - [Attribute] Collection, Location & Topics: Set each of their Defined Type fields to the appropriate types you created earlier
  - [Attribute] Photographer: Set it's Group field to the Photographers group you created earlier
  - [Attribute] Photo 1-10 & CurrentPhoto: Set each of their File Type fields to Photo Archive Image
- Upload Form Actiivity:
  - [Action] Select Photographer: Change the ID in the Text Value field to your Photographers group ID
  - [Action] Form: Upload Photos: Update the Lava include path in the Form Header field, or replace the include with the content of photo-archive-upload-photos.lava
  * Note on the Upload Form activity:
  For some reason when the Prefill Date and Location actions were enabled, the date and location attributes on the resulting photo defined values were always saved as null, regardless of what the user selected on the form, so I temporarily disabled them.
  
  Please DM me if you happen to figure out what's going on. I haven't had a chance to do much testing, but I'm hoping it's just a bug that will be fixed in v13.
- Process Photo Activity:
  - [Attribute] Photo Details: Set the Defined Type field to Photo Archive Photo
  - [Action] Create Photo Details Record: Change the DefinedTypeID parameter value to your photo defined type ID
Update Existing Photos
- Workflow:
  - [Attribute] Collection, Location & Topics: Set their Defined Type fields to the new types you created earlier
  - [Attribute] Photographer: Set it's Group field to the Photographers group you created earlier
- Edit Details Form Activity:
  - [Action] Prefill Photographer: Replace the value assigned groupID in the Lava to your Photographers group ID
- Process Photo Activity:
  - [Attribute] CurrentPhoto: Set the Defined Type field to Photo Archive Photo
Delete Photos
- No changes necessary
Admin Pages

There are nine pages that need to be created for the admin portion of the photo archive. You can place them wherever you want within the site. Just be aware of any permissions that will be inherited from parent pages.

Some of the pages also assume that Rock's built-in workflow entry page already exists at /WorkflowEntry/{WorkflowTypeId}

Photo Archive Page
- Name/Title: Photo Archive
- Site: Rock RMS
- Layout: Full Width
- Icon CSS Class: fas fa-images
- Page Routes: photo-archive
- Security:
 - Rock Admin & Staff Workers Allow View
 - All users Deny View
- Blocks:
 - HTML Content
 - Block Properties > Enabled Lava Commands: SQL
 - Block Properties > Cache Duration: 60
 - HTML:
```
{%- include '~~/assets/lava/custom/photo-archive-photo-stats.lava' -%}
```
 Update the path to match your folder structure, or replace the include with the content of photo-archive-photo-stats.lava.
 - Page Menu (leave default settings)
Photos Page
- Parent Page: Photo Archive
- Name/Title: Photos
- Site: Rock RMS
- Layout: Left Sidebar
- Icon CSS Class: fas fa-images
- Page Routes: photo-archive/photos
- Security: (inherit)
- Blocks:
 - HTML Content
 Since collections can be deleted without deleting the photos in the collections, I added this button so that orphaned photos can still be found and either deleted or added to a collection.
 - Zone: Feature
 - Block Properties > Enabled Lava Commands: SQL
 - Block Properties > Cache Duration: 60
 - HTML:
```
{%- include '~~/assets/lava/custom/photo-archive-orphans-button.lava' -%}
```
 Update the path to match your folder structure, or replace the include with the content of photo-archive-orphans-button.lava.
 - Security:
 - Rock Admin & Photo Archive Admin Allow View
 - All users Deny View
 - Page Parameter Filter
 - Zone: Sidebar 1
 - Filters Per Row: 1
 - Filters:
 - Collections
 
 Key: Collections
 
 Field Type: Defined Value
 
 Defined Type: Photo Archive Collections
 
 Allow Multiple Values: Yes
 
 Enhance For Long Lists: Yes
 - Topics
 
 Key: Topics
 
 Field Type: Defined Value
 
 Defined Type: Photo Archive Topics
 
 Allow Multiple Values: Yes
 
 Enhance For Long Lists: Yes
 - Photographer
 
 Key: Photographer
 
 Field Type: Person
 
 Enable Self Selection: Yes
 - Locations
 
 Key: Locations
 
 Field Type: Defined Value
 
 Defined Type: Photo Archive Locations
 
 Allow Multiple Values: Yes
 
 Enhance For Long Lists: Yes
 - Date Range
 
 Key: Dates
 
 Field Type: Date Range
 - Dynamic Data
 - Zone: Main
 - Query: Replace the photos defined type ID and various defined value attribute IDs at the top of the query
```
DECLARE @Photo_DefinedTypeID AS integer = 129
DECLARE @Photo_CollectionAttrID AS integer = 14767
DECLARE @Photo_FileAttrID AS integer = 14759
DECLARE @Photo_TopicAttrID AS integer = 14762
DECLARE @Photo_DateAttrID AS integer = 14761
DECLARE @Photo_LocAttrID AS integer = 14792
DECLARE @Photo_PhotographerAttrID AS integer = 14760
DECLARE @Collection_DateAttrID AS integer = 14764
DECLARE @Collection_LocAttrID AS integer = 14793

DECLARE @MinDate AS datetime2 = NULL
DECLARE @MaxDate AS datetime2 = NULL
IF RTRIM(@Dates) != '' BEGIN
 SELECT * INTO #DATES FROM STRING_SPLIT(@Dates, ',', 1)
 SELECT @MinDate = CASE WHEN value = '' THEN NULL ELSE CAST(value AS datetime2) END FROM #DATES WHERE ordinal = 1
 SELECT @MaxDate = CASE WHEN value = '' THEN NULL ELSE CAST(value AS datetime2) END FROM #DATES WHERE ordinal = 2
 DROP TABLE IF EXISTS #DATES
END

SELECT value INTO #COLLECTIONS FROM STRING_SPLIT(@Collections, ',')
SELECT value INTO #LOCATIONS FROM STRING_SPLIT(@Locations, ',')
SELECT value INTO #TOPICS FROM STRING_SPLIT(@Topics, ',')

DECLARE @OrphanedOnly AS bit = ISNULL(TRY_CAST(@Orphaned AS bit), 0)

SELECT P.ID, 
 C.Value AS [Collection], C.GUID AS CollectionGUID, 
 ISNULL(PDA.ValueAsDateTime, CDA.ValueAsDateTime) AS [Date],
 CASE WHEN PLA.Value = '' THEN CLA.Value ELSE ISNULL(PLA.Value, CLA.Value) END AS LocationGUID,
 PPA.Value AS PhotographerGUID, P.[Order]
INTO #RESULTS
FROM DefinedValue P LEFT JOIN
 AttributeValue CA ON CA.EntityID = P.ID AND CA.AttributeID = @Photo_CollectionAttrID LEFT JOIN
 DefinedValue C ON CAST(C.GUID AS varchar(36)) = CA.Value LEFT JOIN
 AttributeValue PTA ON PTA.EntityID = P.ID AND PTA.AttributeID = @Photo_TopicAttrID LEFT JOIN
 AttributeValue PDA ON PDA.EntityID = P.ID AND PDA.AttributeID = @Photo_DateAttrID LEFT JOIN
 AttributeValue CDA ON CDA.EntityID = C.ID AND CDA.AttributeID = @Collection_DateAttrID LEFT JOIN
 AttributeValue PLA ON PLA.EntityID = P.ID AND PLA.AttributeID = @Photo_LocAttrID LEFT JOIN
 AttributeValue CLA ON CLA.EntityID = C.ID AND CLA.AttributeID = @Collection_LocAttrID LEFT JOIN
 AttributeValue PPA ON PPA.EntityID = P.ID AND PPA.AttributeID = @Photo_PhotographerAttrID
WHERE P.DefinedTypeID = @Photo_DefinedTypeID
 AND (@Collections = '' OR CAST(CA.Value AS varchar(36)) IN (SELECT value FROM #COLLECTIONS))
 AND (@Topics = '' OR EXISTS(SELECT * FROM STRING_SPLIT(PTA.Value, ',') WHERE value IN (SELECT value FROM #TOPICS)))
 AND (@Locations = '' OR CAST(CASE WHEN PLA.Value = '' THEN CLA.Value ELSE ISNULL(PLA.Value, CLA.Value) END AS varchar(36)) IN (SELECT value FROM #LOCATIONS))
 AND (@MinDate IS NULL OR ISNULL(PDA.ValueAsDateTime, CDA.ValueAsDateTime) >= @MinDate)
 AND (@MaxDate IS NULL OR ISNULL(PDA.ValueAsDateTime, CDA.ValueAsDateTime) <= @MaxDate)
 AND (@Photographer = '' OR CAST(PPA.Value AS varchar(36)) = @Photographer)
 AND ((@OrphanedOnly = 1 AND C.ID IS NULL) OR (@OrphanedOnly = 0 AND C.ID IS NOT NULL))

SELECT R.ID, 
 F.GUID AS PhotoFileGUID, F.FileSize, F.Width, F.Height, 
 R.[Collection], R.CollectionGUID, 
 R.[Date], L.Value AS [Location], R.LocationGUID,
 PR.NickName AS PhotographerFirst, PR.LastName AS PhotographerLast, R.PhotographerGUID
FROM #RESULTS R INNER JOIN
 AttributeValue FA ON FA.EntityID = R.ID AND FA.AttributeID = @Photo_FileAttrID INNER JOIN
 BinaryFile F ON F.GUID = FA.Value LEFT JOIN
 DefinedValue L ON CAST(L.GUID AS varchar(36)) = R.LocationGUID LEFT JOIN
 PersonAlias PA ON CAST(PA.GUID AS varchar(36)) = R.PhotographerGUID LEFT JOIN
 Person PR ON PR.ID = PA.PersonID 
ORDER BY R.[Date] DESC, R.[Collection], R.[Order]

DROP TABLE IF EXISTS #DATES
DROP TABLE IF EXISTS #COLLECTIONS
DROP TABLE IF EXISTS #LOCATIONS
DROP TABLE IF EXISTS #TOPICS
DROP TABLE IF EXISTS #RESULTS
```
 - Parameters: Collections=;Topics=;Dates=;Locations=;Photographer=;Orphaned=
 - Customize Results with Lava: Yes
 - Formatted Output:
```
{%- include '~~/assets/lava/custom/photo-archive-photo-list.lava' -%}
```
 Update the path to match your folder structure, or replace the include with the content of photo-archive-photo-list.lava.
Photo Collections Page
- Parent Page: Photo Archive
- Name/Page Title: Collections
- Browser Title: Photo Archive Collections
- Site: Rock RMS
- Layout: Full Width
- Icon CSS Class: fas fa-folder-open
- Page Routes: photo-archive/collections
- Security:
 - Rock Admin & Photo Archive Admin Allow View
 - All users Deny View
- Block: Defined Value List
 - Defined Type: Photo Archive Collections
 - Custom Column
 - Column Position: Last Column
 - Offset: -1
 - Header Text: Photos
 - Header/Item Class: text-center
 - Lava Template: Replace the photo defined value's collection attribute ID
```
{%- attributevalue Where:'AttributeId == 1234 && Value == "{{ Row.Guid }}"' count:'true' securityenabled:'false' -%}
 <a href="/photo-archive/photos?Collections={{ Row.Guid }}" class="label label-{% if count > 0 %}info{% else %}default{% endif %} p-2">
 {{ count | Format:'N0' }}
 </a>
{%- endattributevalue -%}
```
 - Security: Admin role Allow Edit
Photo Topics Page
- Parent Page: Photo Archive
- Name/Page Title: Topics
- Browser Title: Photo Archive Topics
- Site: Rock RMS
- Layout: Full Width
- Icon CSS Class: fas fa-tags
- Page Routes: photo-archive/topics
- Security:
 - Rock Admin & Photo Archive Admin Allow View
 - All users Deny View
- Block: Defined Value List
 - Defined Type: Photo Archive Topics
 - Custom Column
 - Column Position: Last Column
 - Offset: -1
 - Header Text: Photos
 - Header/Item Class: text-center
 - Lava Template: Replace the photo defined value's topics attribute ID
```
{%- attributevalue Where:'AttributeId == 1234 && Value *= "{{ Row.Guid }}"' count:'true' securityenabled:'false' -%}
 <a href="/photo-archive/photos?Topics={{ Row.Guid }}" class="label label-{% if count > 0 %}info{% else %}default{% endif %} p-2">
 {{ count | Format:'N0' }}
 </a>
{%- endattributevalue -%}
```
 - Security: Admin role Allow Edit
Locations Page
- Parent Page: Photo Archive
- Name/Page Title: Locations
- Browser Title: Photo Archive Locations
- Site: Rock RMS
- Layout: Full Width
- Icon CSS Class: fas fa-map-marker
- Page Routes: photo-archive/locations
- Security:
 - Rock Admin & Photo Archive Admin Allow View
 - All users Deny View
- Block: Defined Value List
 - Defined Type: Photo Archive Locations
 - Custom Column
 - Column Position: Last Column
 - Offset: -1
 - Header Text: Photos
 - Header/Item Class: text-center
 - Lava Template: Replace the photo defined value's location attribute ID
```
{%- attributevalue Where:'AttributeId == 1234 && Value *= "{{ Row.Guid }}"' count:'true' securityenabled:'false' -%}
 <a href="/photo-archive/photos?Topics={{ Row.Guid }}" class="label label-{% if count > 0 %}info{% else %}default{% endif %} p-2">
 {{ count | Format:'N0' }}
 </a>
{%- endattributevalue -%}
```
 - Security: Admin role Allow Edit
Photographers List Page
- Parent Page: Photo Archive
- Name/Title: Photographers
- Site: Rock RMS
- Layout: Full Width
- Icon CSS Class: fas fa-camera
- Page Routes: photo-archive/photographers
- Security:
 - Rock Admin & Photo Archive Admin Allow View
 - All users Deny View
- Block: Group Member List
 Skip ahead and create the Photographer Details page before adding this block so that it's available to select as the Detail Page
 - Block Title: Photographers
 - Detail Page: Photographer Details
 - Group: Photographers
 - Show Date Added: Yes
 - Custom Column
 - Column Position: Last Column
 - Offset: -1
 - Header Text: Photos
 - Header/Item Class: text-center
 - Lava Template: Replace the photo defined value's photographer attribute ID
```
{%- assign photographerAttrID = 1234 -%}
{%- groupmember id:'{{ Row.Id }}' securityenabled:'false' -%}
 {%- assign personAliasGuid = groupmember.Person.PrimaryAlias.Guid -%}
{%- endgroupmember -%}
{%- attributevalue Where:'AttributeId == {{ photographerAttrID }} && Value == "{{ personAliasGuid }}"' count:'true' securityenabled:'false' -%}
<a href="/photo-archive/photos?Photographer={{ personAliasGuid }}" 
 class="label label-{% if count > 0 %}info{% else %}default{% endif %} p-2">
 {{ count | Format:'N0' }}
</a>
{%- endattributevalue -%}
```
Photographer Details Page
- Parent Page: Photographers
- Name/Title: Photographer Details
- Site: Rock RMS
- Layout: Full Width
- Icon CSS Class: fas fa-user
- Show Name in Breadcrumb: No
- Page Routes: photo-archive/photographers/details
- Security: (inherit)
- Block: Group Member Detail
 - Show "Move to…" button: No
Don't forget to go back to the Photographers page to add the Group Member List block to the page

Permissions Management Page
- Parent Page: Photo Archive
- Name/Title: Permissions
- Site: Rock RMS
- Layout: Left Sidebar
- Icon CSS Class: fas fa-shield-alt
- Page Routes: photo-archive/permissions
- Security:
 - Rock Admin & Photo Archive Admin Allow View
 - All users Deny View
- Blocks
 - HTML Content
 - Zone: Sidebar 1
 - Block Properties > Enabled Lava Commands: RockEntity
 - HTML:
```
{%- include '~~/assets/lava/custom/photo-archive-security-role-list.lava' -%}
```
 Update the path to match your folder structure, or replace the include with the content of photo-archive-security-role-list.lava.
 - Group Member List
 Skip ahead and create the Security Role Member Details page before adding this block so that it's available to select as the Detail Page
 - Zone: Main
 - Block Title: Role Members
 - Detail Page: Security Role Member
 - Show Date Added: Yes
Security Role Member Details Page
- Parent Page: Permissions
- Name/Title: Security Role Member
- Site: Rock RMS
- Layout: Full Width
- Icon CSS Class: fas fa-user-shield
- Show Name in Breadcrumb: No
- Page Routes: photo-archive/permissions/member
- Security: (inherit)
- Block: Group Member Detail
 - Show "Move to…" button: No
Don't forget to go back to the Permissions page to add the Group Member List block to the page

Public Pages

There are three pages that need to be created for the public side of the photo archive.

Photo Archive Page

Name/Title: Photo Archive
Site: Your Public Website
Layout: Full Width
Icon CSS Class: fas fa-images
Page Routes: photo-archive
Security:
- Rock Admin & Staff Workers Allow View
- Photo Archive Admin, Editor, Uploader & Viewer Allow View
- All users Deny View

Block: Dynamic Data

Query: Replace the photo and collections defined type IDs and various defined value attribute IDs

DECLARE @Collect_DefinedTypeID AS integer = 1234
DECLARE @Photo_DefinedTypeID AS integer = 1234
DECLARE @Photo_CollectionAttrID AS integer = 1234
DECLARE @Photo_FileAttrID AS integer = 1234
DECLARE @Collection_DateAttrID AS integer = 1234
DECLARE @Collection_LocAttrID AS integer = 1234

SELECT P.ImageGUID, P.CollectionGUID 
INTO #COLLECTIONTHUMBS
FROM
    (SELECT CAST(PFA.Value AS uniqueidentifier) AS ImageGUID, CAST(PCA.Value AS uniqueidentifier) AS CollectionGUID,
        Row_number() OVER (PARTITION BY PCA.Value ORDER BY NEWID()) AS RN
    FROM DefinedValue P INNER JOIN
        AttributeValue PCA ON PCA.EntityID = P.ID AND PCA.AttributeID = @Photo_CollectionAttrID LEFT JOIN
        AttributeValue PFA ON PFA.EntityID = P.ID AND PFA.AttributeID = @Photo_FileAttrID
    WHERE P.DefinedTypeID = @Photo_DefinedTypeID) P
WHERE P.RN = 1

SELECT PCA.Value AS CollectionGUID, COUNT(*) AS PhotoCount
INTO #PHOTOCOUNTS
FROM DefinedValue P INNER JOIN 
    AttributeValue PCA ON PCA.EntityID = P.ID AND PCA.AttributeID = @Photo_CollectionAttrID
WHERE P.DefinedTypeID = @Photo_DefinedTypeID
GROUP BY PCA.Value

SELECT C.ID AS ID, C.GUID, C.Value AS [Name], CDA.ValueAsDateTime AS [Date], L.Value AS [Location],
    TH.ImageGUID, ISNULL(PC.PhotoCount, 0) AS PhotoCount
FROM DefinedValue C LEFT JOIN
    #PHOTOCOUNTS PC ON PC.CollectionGUID = C.GUID LEFT JOIN
    #COLLECTIONTHUMBS TH ON TH.CollectionGUID = C.GUID LEFT JOIN
    AttributeValue CDA ON CDA.EntityID = C.ID AND CDA.AttributeID = @Collection_DateAttrID LEFT JOIN
    AttributeValue CLA ON CLA.EntityID = C.ID AND CLA.AttributeID = @Collection_LocAttrID LEFT JOIN
    DefinedValue L ON L.GUID = CAST(CLA.Value AS uniqueidentifier)
WHERE C.DefinedTypeID = @Collect_DefinedTypeID
ORDER BY CDA.ValueAsDateTime DESC, C.Value

DROP TABLE IF EXISTS #COLLECTIONTHUMBS
DROP TABLE IF EXISTS #PHOTOCOUNTS

Customize Results with Lava: Yes
Formatted Output:
```
{%- include '~/themes/rock/assets/lava/custom/photo-archive-collection-list.lava' -%}
```
Update the path to match your folder structure, or replace the include with the content of photo-archive-collection-list.lava.

Photos Page

Parent Page: Photo Archive
Name/Title: Photos
Site: Your Public Website
Layout: Full Width
Icon CSS Class: fas fa-folder-open
Page Routes: photo-archive/photos
Security: (inherit)

Block: Dynamic Data

Query: Replace the photos defined type ID and various defined value attribute IDs

DECLARE @Photo_DefinedTypeID AS integer = 1234
DECLARE @Photo_CollectionAttrID AS integer = 1234
DECLARE @Photo_FileAttrID AS integer = 1234
DECLARE @Photo_TopicAttrID AS integer = 1234
DECLARE @Photo_DateAttrID AS integer = 1234
DECLARE @Photo_LocAttrID AS integer = 1234
DECLARE @Photo_PhotographerAttrID AS integer = 1234
DECLARE @Collection_DateAttrID AS integer = 1234
DECLARE @Collection_LocAttrID AS integer = 1234

DECLARE @MinDate AS datetime2 = NULL
DECLARE @MaxDate AS datetime2 = NULL
IF RTRIM(@Dates) != '' BEGIN
    SELECT * INTO #DATES FROM STRING_SPLIT(@Dates, ',', 1)
    SELECT @MinDate = CASE WHEN value = '' THEN NULL ELSE CAST(value AS datetime2) END FROM #DATES WHERE ordinal = 1
    SELECT @MaxDate = CASE WHEN value = '' THEN NULL ELSE CAST(value AS datetime2) END FROM #DATES WHERE ordinal = 2
    DROP TABLE IF EXISTS #DATES
END

SELECT value INTO #COLLECTIONS FROM STRING_SPLIT(@Collections, ',')
SELECT value INTO #LOCATIONS FROM STRING_SPLIT(@Locations, ',')
SELECT value INTO #TOPICS FROM STRING_SPLIT(@Topics, ',')

SELECT P.ID, 
    C.Value AS [Collection], C.GUID AS CollectionGUID, 
    ISNULL(PDA.ValueAsDateTime, CDA.ValueAsDateTime) AS [Date],
    CASE WHEN PLA.Value = '' THEN CLA.Value ELSE ISNULL(PLA.Value, CLA.Value) END AS LocationGUID,
    PPA.Value AS PhotographerGUID, P.[Order]
INTO #RESULTS
FROM DefinedValue P INNER JOIN
    AttributeValue CA ON CA.EntityID = P.ID AND CA.AttributeID = @Photo_CollectionAttrID INNER JOIN
    DefinedValue C ON CAST(C.GUID AS varchar(36)) = CA.Value LEFT JOIN
    AttributeValue PTA ON PTA.EntityID = P.ID AND PTA.AttributeID = @Photo_TopicAttrID LEFT JOIN
    AttributeValue PDA ON PDA.EntityID = P.ID AND PDA.AttributeID = @Photo_DateAttrID LEFT JOIN
    AttributeValue CDA ON CDA.EntityID = C.ID AND CDA.AttributeID = @Collection_DateAttrID LEFT JOIN
    AttributeValue PLA ON PLA.EntityID = P.ID AND PLA.AttributeID = @Photo_LocAttrID LEFT JOIN
    AttributeValue CLA ON CLA.EntityID = C.ID AND CLA.AttributeID = @Collection_LocAttrID LEFT JOIN
    AttributeValue PPA ON PPA.EntityID = P.ID AND PPA.AttributeID = @Photo_PhotographerAttrID
WHERE P.DefinedTypeID = @Photo_DefinedTypeID
    AND (@Collections = '' OR CAST(CA.Value AS varchar(36)) IN (SELECT value FROM #COLLECTIONS))
    AND (@Topics = '' OR EXISTS(SELECT * FROM STRING_SPLIT(PTA.Value, ',') WHERE value IN (SELECT value FROM #TOPICS)))
    AND (@Locations = '' OR CAST(CASE WHEN PLA.Value = '' THEN CLA.Value ELSE ISNULL(PLA.Value, CLA.Value) END AS varchar(36)) IN (SELECT value FROM #LOCATIONS))
    AND (@MinDate IS NULL OR ISNULL(PDA.ValueAsDateTime, CDA.ValueAsDateTime) >= @MinDate)
    AND (@MaxDate IS NULL OR ISNULL(PDA.ValueAsDateTime, CDA.ValueAsDateTime) <= @MaxDate)
    AND (@Photographer = '' OR CAST(PPA.Value AS varchar(36)) = @Photographer)

SELECT R.ID, 
    F.GUID AS PhotoFileGUID, F.FileSize, F.Width, F.Height, 
    R.[Collection], R.CollectionGUID, 
    R.[Date], L.Value AS [Location], R.LocationGUID,
    PR.NickName AS PhotographerFirst, PR.LastName AS PhotographerLast, R.PhotographerGUID
FROM #RESULTS R INNER JOIN
    AttributeValue FA ON FA.EntityID = R.ID AND FA.AttributeID = @Photo_FileAttrID INNER JOIN
    BinaryFile F ON F.GUID = FA.Value LEFT JOIN
    DefinedValue L ON CAST(L.GUID AS varchar(36)) = R.LocationGUID LEFT JOIN
    PersonAlias PA ON CAST(PA.GUID AS varchar(36)) = R.PhotographerGUID LEFT JOIN
    Person PR ON PR.ID = PA.PersonID 
ORDER BY R.[Date] DESC, R.[Collection], R.[Order]

DROP TABLE IF EXISTS #DATES
DROP TABLE IF EXISTS #COLLECTIONS
DROP TABLE IF EXISTS #LOCATIONS
DROP TABLE IF EXISTS #TOPICS
DROP TABLE IF EXISTS #RESULTS

Parameters: Collections=;Topics=;Dates=;Locations=;Photographer=
Customize Results with Lava: Yes

Formatted Output:

{%- assign workflowEntryRoute = 'photo-archive/photos/{Task}/{WorkflowTypeId}' -%}
{%- include '~/themes/Rock/assets/lava/custom/photo-archive-photo-list.lava' -%}

Update the Lava file path to match your folder structure, or replace the include with the content of photo-archive-photo-list.lava.

Actions Page

Parent Page: Photos
Name/Page Title: Actions
Browser Title: Photo Actions
Site: Your Public Website
Layout: Full Width
Icon CSS Class: fas fa-edit
Page Routes: photo-archive/photos/{Task}/{WorkflowTypeId}
Security: (inherit)
Block: Workflow Entry
- No special settings necessary

Go to the admin Photo Archive page and start testing everything by adding collections, topics, locations and photos.

Follow Up

Please don't hesitate to leave a comment below or hit me up on Rock Chat (@JeffRichmond) if you have questions or find any issues with this recipe.

I have a couple concerns with the recipe at this point. The biggest one is how well all of this will scale as we continue adding more photos. ~~At some point I may have to find better ways to optimize the photo search page.~~ I have made some query improvements that make the load times on the photo pages almost instant, but I'm still not sure how those pages will perform with a lot more data.

The other much more minor concern is that Rock doesn't allow an easy way to select multiple files and upload them all once. As workaround, the upload workflow actually has 10 separate image attributes so the user can at least select and upload up to 10 photos. I'd love to find a solution that allows the user to browse for their files and select everything they want to upload just one time, rather than having to select one photo at a time and being limited to a maximum of 10 before starting all over again with the next 10.

If you come up with better or more efficient ways of doing anything in this recipe, please let me know. Thanks!

Change Log

2023-02-02 - Fixed error when the photographer or location is left blank on a photo
2022-05-16 - Added photo count to photo pages
2022-03-24 - Improved load times for the photo pages
2022-03-18 - Initial Version

Download File

Recipe

Photo Archive

Photo Archive

Background

Summary

Prerequisites

How-To

Security Roles

File Type

Global Attribute

Photographers Group

Defined Types

Photo Archive Locations

Photo Archive Topics

Photo Archive Collections

Photo Archive Photos

Photoswipe.js

Lava Files

Workflow Types

Upload New Photos

Update Existing Photos

Delete Photos

Admin Pages

Photo Archive Page

Photos Page

Photo Collections Page

Photo Topics Page

Locations Page

Photographers List Page

Photographer Details Page

Permissions Management Page

Security Role Member Details Page

Public Pages

Photo Archive Page

Photos Page

Actions Page

Follow Up

Change Log

Login to add a comment...