Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Find similar assets in db by comparing tags

Author  Topic 

Snory
Starting Member

2 Posts

Posted - 2010-11-11 : 14:03:02
For a CMS project I am working on, I was given the requirement
to find similar assets in the db by comparing tags.

Any time a user visited an asset detail page, they would be
provided with a list of other assets in the db that were
most similar to the one they were currently looking at.

Here is the example I came up with.
I was hoping to get some feedback on this sql.
(or links to other other strategies)

The script will create the tables, insert seed data, and provide several examples of extracting info. You must first create the db.

This script was tested on SQL2005,2008

thanks
Snory




Set Nocount On


/* *************************** MOST POPULAR TAGS *************************** */
/* The goal of this demo is to find out what assets in my database are most */
/* similar to other assets in the database by comparing tags */
/* */
/* For this example, there are 3 tables used */
/* Asset - Contains list of assets, images, objects ... */
/* Tags - Contains a unique list of all tags within the system */
/* AssetTagRel - Many to Many rel between assets and tags */
/* */
/* For seed Data */
/* Asset A -> Related to Tags 1,2 */
/* Asset B -> Related to Tags 1,2 */
/* Asset C -> Related to Tags 1,2,3 */
/* Asset D -> Related to Tags 1,3,4 */
/* Asset E -> Related to Tag 5 */
/* */
/* ************************************************************************* */



/* ************************************************************************* */
/* ************************* DROP PREVIOUS VERSION ************************* */
/* ************************************************************************* */

/* Drop Tables if they already exist */
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AssetTagRel')
DROP TABLE AssetTagRel
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Assets')
DROP TABLE Assets
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Tags')
DROP TABLE Tags
GO

/* ************************************************************************* */
/* ***************************** CREATE TABLES ***************************** */
/* ************************************************************************* */

CREATE TABLE [Assets]
(
[AssetId] [uniqueidentifier] NOT NULL,
[Title] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED
(
[AssetId] ASC
)
)

CREATE TABLE [Tags]
(
[TagId] [uniqueidentifier] NOT NULL,
[Title] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED
(
[TagId] ASC
)
)

CREATE TABLE [AssetTagRel]
(
[AssetTagRelId] [uniqueidentifier] NOT NULL,
[AssetId] [uniqueidentifier] NOT NULL,
[TagId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_AssetTagRel] PRIMARY KEY CLUSTERED ([AssetTagRelId] ASC),
CONSTRAINT [FK_AssetTagRel_Asset] FOREIGN KEY ([AssetId]) REFERENCES [Assets] ([AssetId]),
CONSTRAINT [FK_AssetTagRel_Tag] FOREIGN KEY ([TagId]) REFERENCES [Tags] ([TagId])
)

/* ************************************************************************* */
/* ******************************* SEED DATA ******************************* */
/* ************************************************************************* */

/* Seed Data - Assets */
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000A','Asset A')
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000B','Asset B')
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000C','Asset C')
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000D','Asset D')
Insert Into Assets Values ('00000000-0000-0000-0000-00000000000E','Asset E')

/* Seed Data - Tags */
Insert Into Tags Values ('00000000-0000-0000-0000-000000000001','Tag 1')
Insert Into Tags Values ('00000000-0000-0000-0000-000000000002','Tag 2')
Insert Into Tags Values ('00000000-0000-0000-0000-000000000003','Tag 3')
Insert Into Tags Values ('00000000-0000-0000-0000-000000000004','Tag 4')
Insert Into Tags Values ('00000000-0000-0000-0000-000000000005','Tag 5')

/* Seed Data - Relationships for Asset A */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000001')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000002')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000003') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000004') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000A','00000000-0000-0000-0000-000000000005') */

/* Seed Data - Relationships for Asset B */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000001')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000002')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000003') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000004') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000B','00000000-0000-0000-0000-000000000005') */

/* Seed Data - Relationships for Asset C */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000001')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000002')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000003')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000004') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000C','00000000-0000-0000-0000-000000000005') */

/* Seed Data - Relationships for Asset D */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000001')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000002') */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000003')
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000004')
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000D','00000000-0000-0000-0000-000000000005') */

/* Seed Data - Relationships for Asset E */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000001') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000002') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000003') */
/* Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000004') */
Insert Into AssetTagRel Values (NewId(),'00000000-0000-0000-0000-00000000000E','00000000-0000-0000-0000-000000000005')


/* ************************************************************************* */
/* ******************************* REPORTING ******************************* */
/* ************************************************************************* */

/* List of Assets */
PRINT 'List of Assets'
SELECT
AssetId,
Title
FROM
Assets
ORDER BY
Title

PRINT ''
PRINT ''

/* List of Tags */
PRINT 'List of Tags'
SELECT
[TagId],
[Title]
FROM
[Tags]
ORDER BY
Title

PRINT ''
PRINT ''

/* Display Unique List of Tags that 'Asset A' has in common with 'Asset B' */
PRINT 'Display Unique List of Tags that ''Asset A'' has in common with ''Asset B'''
SELECT TagId FROM AssetTagRel WHERE AssetId = '00000000-0000-0000-0000-00000000000A'
Intersect
SELECT TagId FROM AssetTagRel WHERE AssetId = '00000000-0000-0000-0000-00000000000B'

PRINT ''
PRINT ''

/* List all other assets that are in some way related to 'Asset A' */
PRINT 'List all other assets that are in some way related to ''Asset A'''
SELECT a2.AssetId
FROM
(
SELECT TagId
FROM AssetTagRel
WHERE AssetId = '00000000-0000-0000-0000-00000000000A'
) AS a1
INNER JOIN AssetTagRel a2 ON
a1.TagId = a2.TagId
AND AssetId <> '00000000-0000-0000-0000-00000000000A'
GROUP BY
a2.AssetId

PRINT ''
PRINT ''

/* List all other assets that are the most similar to 'Asset A' sorted by most common relationships / intersections */
PRINT 'List all other assets that are the most similar to ''Asset A'' sorted by most common relationships / intersections '
SELECT a2.AssetId,
(
SELECT COUNT(TagId)
FROM
(
select TagId from AssetTagRel where AssetId = a1.AssetId
Intersect
select TagId from AssetTagRel where AssetId = a2.AssetId
) IntersectionList
) IntersectionCount
FROM
(
SELECT TagId, AssetId
FROM AssetTagRel
WHERE AssetId = '00000000-0000-0000-0000-00000000000A'
) AS a1
INNER JOIN AssetTagRel a2 ON
a1.TagId = a2.TagId
AND a2.AssetId <> '00000000-0000-0000-0000-00000000000A'
GROUP BY
a1.AssetId,
a2.AssetId
Order by IntersectionCount DESC
   

- Advertisement -