Getting PhotoUrl in pure SQL

  • By Michael Garrison One Year Ago

I've got a page that is part of my checkout procedure. It's just got a Dynamic Data block on it and just outputs a list of people who are presently checked in. But I like displaying the person's photo next to their name.

The full query I used is:

SELECT
    a.[Id]
    ,'<img src="/GetImage.ashx?id=' + CAST(p.[PhotoId] as varchar(10)) + '" style="max-width:50px;max-height:50px;"> ' + p.[LastName] + ', ' + p.[NickName] AS "Person"
    ,g.[Name] "Group"
    ,l.[Name] "Location"
    --,s.[Name] "Schedule"
    ,a.[StartDateTime]
    --,c.[Name] "Campus"
FROM
    [Attendance] a
    LEFT JOIN [PersonAlias] pa ON a.[PersonAliasId]=pa.[Id]
    LEFT JOIN [Person] p ON pa.[PersonId] = p.[Id]
    LEFT JOIN [Group] g ON a.[GroupId]=g.[Id]
    --LEFT JOIN [Schedule] s ON a.[ScheduleId]=s.[Id]
    LEFT JOIN [Location] l ON a.[LocationId]=l.[Id]
    --LEFT JOIN [Campus] c ON a.[CampusId]=c.[Id]
WHERE
    a.[ScheduleId] IS NOT NULL
   --AND a.[StartDateTime] >= DATEADD(HOUR, -1, GETDATE())
    AND a.[StartDateTime] > '{{ "Now" | Date:"yyyy-M-d" }}'
    AND a.[EndDateTime] IS NULL
ORDER BY
    p.[LastName],p.[NickName] ASC

It worked great in my tests. But the first weekend we tried it with live data, we found out that if a person didn't have a profile photo, that entire cell was blank. It didn't even list their name!

What I really wanted was something like Lava's {{ Person.PhotoUrl }} property - it shows their profile picture if it exists, or one of the four "default" silhouettes based on their gender and age. But the problem with that was that the Lava runs long before the SQL would be able to provide the PersonId to get the image for.

It's possible to use WHEN p.[PhotoId] IS NOT NULL THEN to provide different content if they don't have a photo. But I like keeping things neat and in line in the grid.

So ... a SQL User Defined Function was called for.

Copy this into Admin Tools > Power Tools > SQL Command (be sure to flip Selection Query to 'no' or it will look like an error occurred) and run it:

CREATE FUNCTION dbo.PhotoUrl (
	@PersonId int
)
RETURNS VARCHAR(64)
AS
BEGIN
	DECLARE @StoredPhotoId integer, @Gender integer, @Age float, @Ret varchar(64);
	SET @StoredPhotoId = (SELECT p.[PhotoId] FROM [Person] p WHERE p.[Id] = @PersonId);
	IF @StoredPhotoId IS NOT NULL
	BEGIN
		SET @Ret = (SELECT '/GetImage.ashx?id=' + CAST(@StoredPhotoId AS VARCHAR(10)));
	END
	ELSE
	BEGIN
		SET @Gender = (SELECT p.[Gender] FROM [Person] p WHERE p.[Id] = @PersonId);
		SET @Age = (SELECT DATEDIFF(year, p.[BirthDate], GETDATE()) FROM [Person] p WHERE p.[Id] = @PersonId);
		IF @Age < 18
		BEGIN
			IF @Gender = 2
			BEGIN
				SET @Ret = (SELECT '/Assets/Images/person-no-photo-child-female.svg?');
			END
			ELSE
			BEGIN
				SET @Ret = (SELECT '/Assets/Images/person-no-photo-child-male.svg?');
			END
		END
		ELSE
		BEGIN
			IF @Gender = 2
			BEGIN
				SET @Ret = (SELECT '/Assets/Images/person-no-photo-female.svg?')
			END
			ELSE
			BEGIN
				SET @Ret = ('/Assets/Images/person-no-photo-male.svg?')
			END
		END
	END
	RETURN @ret;
END

Just like that, you've got a new function that you can run any time right from SQL.

So change the first three lines in the initial checkout list query to:

SELECT
    a.[Id]
    ,'<img src="' + dbo.PhotoUrl(p.[Id]) + '" style="max-width:50px;max-height:50px;"> ' + p.[LastName] + ', ' + p.[NickName] AS "Person"

and you're off and running!

PhotoUrlUDF.PNG


@mikejed
Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!