Just sharing a small change I made that I thought others might be interested in.
I plan to add a dynamic link next to each photo that links to the search page with the "createdby" user as the search term, so that the visitor more photos posted by that the contributing user.
By adding the following two sections to the search stored procedure I have added user name to the search results so that if a user ads pictures to albums that they do not own, you can find their pictures anywhere in the gallery.
-- Created By Album
INSERT #searchResults
SELECT 'a', a.AlbumId, 'Album.CreatedBy', '%' + SUBSTRING(a.CreatedBy,
PATINDEX(#searchTerms.searchTerm, a.CreatedBy),
LEN(#searchTerms.searchTerm) - 2) + '%'
FROM dbo.[gs_Album] a CROSS JOIN #searchTerms
WHERE a.FKGalleryId = @GalleryId AND a.CreatedBy LIKE #searchTerms.searchTerm
-- Created by - Images
INSERT #searchResults
SELECT 'm', m.MediaObjectId, 'MediaObject.CreatedBy', '%' + SUBSTRING(m.CreatedBy,
PATINDEX(#searchTerms.searchTerm, m.CreatedBy),
LEN(#searchTerms.searchTerm) - 2) + '%'
FROM dbo.[gs_MediaObject] m JOIN dbo.[gs_Album] a ON a.AlbumID =
m.FKAlbumId CROSS JOIN #searchTerms
WHERE a.FKGalleryId = @GalleryId AND m.CreatedBy LIKE #searchTerms.searchTerm
-- Whole Script -
--(From version 2.4, may or may not be compatible with 2.46)
--Drop and then re-create the stored procedure.
drop procedure [gs_SearchGallery]
/****** Object: StoredProcedure [dbo].[gs_SearchGallery] Script Date: 03/20/2011 20:57:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[gs_SearchGallery]
( @SearchTerms nvarchar(4000), @GalleryId int)
AS
SET NOCOUNT ON
/* Search for albums and media objects that match the specified search
terms. The album or media object must match
ALL search terms to be considered a match. There is no 'OR' capability. For
albums, the Title and Summary columns
are searched. For media objects, the Title and OriginalFilename columns and
all metadata in the MediaObjectMetadata
tables is searched.
Inputs:
@SearchTerms - A comma-delimited set of search terms. May include spaces.
Ex: "cat,dog", "cat videos, dog videos"
Multiple words in a single search term (such as "cat videos" in the
previous example) are treated as a phrase
that must be matched, exactly like how Google treats phrases contained in
quotation marks. That is, "cat videos"
requires the phrase "cat videos" to appear somewhere in the data, and it
will not match "cat and dog videos"
(to match "cat and dog videos", you can use "cat,videos").
@GalleryId - The ID of the gallery to search.
Returns:
Returns a set of records with two columns:
gotype - A single character containing either 'a' for album or 'm' for media
object. Indicates whether the id
stored in the second column is an album or media object.
id - The ID of a matching album or media object.
Algorithm:
The search follows these steps:
1. Create a temporary table #searchTerms and insert the search terms into
it, prepending and appending the wildcard
character (%). Ex: If @SearchTerms = "cat videos,dog,fish", #searchTerms
will get 3 records: %cat videos%,
%dog%, %fish%.
2. Create a second temporary table #searchResults to hold intermediate
search results.
3. Insert into #searchResults all albums where the title matches one of more
search terms. There will be one record
inserted for each matching search term. Ex: If @SearchTerms = "cat
videos,dog,fish" and the album title =
"My dog and cat videos", there will be two records inserted into
#searchResults, one with matchingSearchTerm =
"%cat videos%" and the other "%dog%" (gotype='a', id=album ID,
fieldname='Album.Title' for both).
4. Repeat the above step for other fields: Album.Summary, MediaObject.Title,
MediaObject.OriginalFilename, and
all media object metadata for each media object
5. Select those records from #searchResults where we made a successful match
for EVERY search term for each album or
media object.
Note: The fieldname column in #searchResults is not used except for manual
debugging purposes. This column can
be removed if desired.
*/
CREATE TABLE #searchTerms (searchTerm nvarchar(4000) COLLATE database_default)
CREATE TABLE #searchResults (
gotype char(1),
id int,
fieldname nvarchar(50) COLLATE database_default,
matchingSearchTerm nvarchar(3000) COLLATE database_default)
INSERT #searchTerms
SELECT '%' + nstr + '%' FROM
dbo.[gs_func_convert_string_array_to_table](@SearchTerms, ',')
-- Search album title
INSERT #searchResults
SELECT 'a', a.AlbumId, 'Album.Title', '%' + SUBSTRING(a.Title,
PATINDEX(#searchTerms.searchTerm, a.Title),
LEN(#searchTerms.searchTerm) - 2) + '%'
FROM dbo.[gs_Album] a CROSS JOIN #searchTerms
WHERE a.FKGalleryId = @GalleryId AND a.Title LIKE #searchTerms.searchTerm
-- Search album summary
INSERT #searchResults
SELECT 'a', a.AlbumId, 'Album.Summary', '%' + SUBSTRING(a.Summary,
PATINDEX(#searchTerms.searchTerm, a.Summary), LEN(#searchTerms.searchTerm) -
2) + '%'
FROM dbo.[gs_Album] a CROSS JOIN #searchTerms
WHERE a.FKGalleryId = @GalleryId AND a.Summary LIKE #searchTerms.searchTerm
-- Search media object title
INSERT #searchResults
SELECT 'm', m.MediaObjectId, 'MediaObject.Title', '%' +
SUBSTRING(m.Title, PATINDEX(#searchTerms.searchTerm,
m.Title), LEN(#searchTerms.searchTerm) - 2) + '%'
FROM dbo.[gs_MediaObject] m JOIN dbo.[gs_Album] a
ON a.AlbumId = m.FKAlbumId CROSS JOIN #searchTerms
WHERE a.FKGalleryId = @GalleryId AND m.Title LIKE
#searchTerms.searchTerm
-- Search media object original filename
INSERT #searchResults
SELECT 'm', m.MediaObjectId, 'MediaObject.OriginalFilename',
'%' + SUBSTRING(m.OriginalFilename,
PATINDEX(#searchTerms.searchTerm, m.OriginalFilename),
LEN(#searchTerms.searchTerm) - 2) + '%'
FROM dbo.[gs_MediaObject] m JOIN dbo.[gs_Album] a ON a.AlbumId =
m.FKAlbumId CROSS JOIN #searchTerms
WHERE a.FKGalleryId = @GalleryId AND m.OriginalFilename
LIKE #searchTerms.searchTerm --AND 0=1
-- Search media object metadata
INSERT #searchResults
SELECT DISTINCT 'm', m.MediaObjectId, 'MediaObjectMetadata',
'%' + SUBSTRING(md.Value,
PATINDEX(#searchTerms.searchTerm, md.Value),
LEN(#searchTerms.searchTerm) - 2) + '%'
FROM dbo.[gs_MediaObjectMetadata] md JOIN dbo.[gs_MediaObject] m
ON md.FKMediaObjectId = m.MediaObjectId
JOIN dbo.[gs_Album] a
ON a.AlbumId = m.FKAlbumId CROSS JOIN #searchTerms
WHERE a.FKGalleryId = @GalleryId AND md.Value
LIKE #searchTerms.searchTerm
-- Created By Album
INSERT #searchResults
SELECT 'a', a.AlbumId, 'Album.CreatedBy', '%' + SUBSTRING(a.CreatedBy,
PATINDEX(#searchTerms.searchTerm, a.CreatedBy),
LEN(#searchTerms.searchTerm) - 2) + '%'
FROM dbo.[gs_Album] a CROSS JOIN #searchTerms
WHERE a.FKGalleryId = @GalleryId AND a.CreatedBy LIKE #searchTerms.searchTerm
-- Created by - Images
INSERT #searchResults
SELECT 'm', m.MediaObjectId, 'MediaObject.CreatedBy', '%' + SUBSTRING(m.CreatedBy,
PATINDEX(#searchTerms.searchTerm, m.CreatedBy),
LEN(#searchTerms.searchTerm) - 2) + '%'
FROM dbo.[gs_MediaObject] m JOIN dbo.[gs_Album] a ON a.AlbumID =
m.FKAlbumId CROSS JOIN #searchTerms
WHERE a.FKGalleryId = @GalleryId AND m.CreatedBy LIKE #searchTerms.searchTerm
-- Uncomment for debug purposes:
--SELECT * from #searchTerms
--SELECT * FROM #searchResults
SELECT sr.gotype, sr.id
FROM #searchTerms AS st INNER JOIN (SELECT DISTINCT gotype, id,
matchingSearchTerm FROM #searchResults) AS sr ON st.searchTerm =
sr.matchingSearchTerm
GROUP BY sr.gotype, sr.id
HAVING (COUNT(*) >= (SELECT COUNT(*) FROM #searchTerms))
RETURN
GO