3 Photo Archive Shared by Jeff Richmond, The Well Community Church one year 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"> <i class="fa fa-images"></i> {{ 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"> <i class="fa fa-images"></i> {{ 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"> <i class="fa fa-images"></i> {{ 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"> <i class="fas fa-images"></i> {{ 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
Jackson Uy Reply 27 days ago Exactly what I wanted to create and you made my journey in migrating our website to Rock so much easier with this photo collection recipe, we have tons of photo albums in our current PhP website. Thank you so much for sharing your recipe. I'll give this a try, would you mind if I ping you in RockChat if I got an issue later? Thanks!