On Feb 28, 8:51=A0am, "Plamen Ratchev" <Pla...@[EMAIL PROTECTED]
> wrote:
> I can only repeat the same as Hugo and Najm, it is hard to say anything
> without seeing the actual table, indexes, sample data, required results,
a=
nd
> business requirements/rules. Reading all those posts in the link at
> sqlteam.com I can see only sample code provided by Peso (Peter Larsson).
T=
he
> problem could be difficult or easy to solve, but hard to say without
seein=
g
> the real requirements.
>
> Perhaps some things to consider are using materialized path or nested
sets=
> to stored the hierarchy of contacts. Those models provide very efficient
> retrieval of distance between nodes info, but have more difficult
methods
> for maintaining data.
>
> Here is just a small sample (using the sample table and data provided by
> Peso) on how materialized path may look:
>
> -- Sample table with data
> CREATE TABLE Contacts (
> =A0c_from CHAR(1),
> =A0c_to CHAR(1),
> =A0PRIMARY KEY (c_from, c_to));
>
> INSERT INTO Contacts
> SELECT 'A', 'B' UNION ALL
> SELECT 'B', 'D' UNION ALL
> SELECT 'C', 'A' UNION ALL
> SELECT 'C', 'E' UNION ALL
> SELECT 'G', 'C' UNION ALL
> SELECT 'B', 'G' UNION ALL
> SELECT 'F', 'D' UNION ALL
> SELECT 'E', 'F';
>
> -- Table to store paths
> CREATE TABLE Paths (c_path VARCHAR(200) PRIMARY KEY);
>
> This is the real hurdle, recalculating all paths
>
> -- Recursive CTE to populate the paths
> WITH PathCTE
> AS
> (SELECT c_from, c_to,
> =A0 =A0 =A0 =A0 =A0 =A0CAST('.' + CAST(c_from AS VARCHAR(8)) + '.' +
> =A0 =A0 =A0 =A0 =A0 =A0CAST(c_to AS VARCHAR(8)) + '.' AS VARCHAR(200))
AS =
c_path
> =A0FROM Contacts AS C1
> =A0UNION ALL
> =A0SELECT C.c_from, C.c_to,
> =A0 =A0 =A0 =A0 =A0 =A0CAST(P.c_path + C.c_to + '.' AS VARCHAR(200))
> =A0FROM PathCTE AS P
> =A0JOIN Contacts AS C
> =A0 =A0ON P.c_to =3D C.c_from
> =A0WHERE P.c_path NOT LIKE '%.' +
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 CAST(C.c_from AS VARCHAR(10)) +
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 '.' +
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 CAST(C.c_to AS VARCHAR(10)) +
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 '.%')
> INSERT INTO Paths
> SELECT c_path FROM PathCTE;
>
> -- Show all paths between B and D
> SELECT c_path
> FROM Paths
> WHERE c_path LIKE '.B.%'
> =A0 =A0AND c_path LIKE '%.D.';
>
> -- Shortest path distance, longest path distance, and number of paths
> SELECT MIN(LEN(c_path) - LEN(REPLACE(c_path, '.', '')) - 2) AS
> shortest_distance,
> =A0 =A0 =A0 =A0 =A0 MAX(LEN(c_path) - LEN(REPLACE(c_path, '.', '')) - 2)
A=
S
> longest_distance,
> =A0 =A0 =A0 =A0 =A0 COUNT(*) AS paths_cnt
> FROM Paths
> WHERE c_path LIKE '.B.%'
> =A0 =A0AND c_path LIKE '%.D.';
>
> Looking at the paths found:
> .B.D.
> .B.G.C.A.B.D.
> .B.G.C.E.F.D.
>
> You may notice the second path reused the first path to reach the
> destination. But this could be desired or not, again hard to say with no
> requirements. Easy to handle but did not bother...
>
> As you can see calculating the distance is easy, but maintenance offsets
> that. Based on your needs and model, if data is static this may do.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
Sorry for not having to replied earlier.
here is the code.
this is the query that gets fired;
SELECT Users.UserID, Users.FirstName + ' ' + Users.LastName as Name,
UserProfile.PropertyValue as Location,
professionalInfo.headline as Headline, industries.industryName as
Industry, professionalInfo.summary as Summary,
professionalInfo.interests, dbo.GetConnectionsCount(Users.UserID) AS
Connections,
dbo.GetRecommendations(Users.UserID) AS Recommendations,
dbo.fnCommonFriendsStep(Users.UserID, 36) AS Degree FROM Users
INNER JOIN UserProfile ON Users.UserID =3D UserProfile.UserID
INNER JOIN professionalInfo ON Users.UserID =3D
professionalInfo.memberId
INNER JOIN industries ON professionalInfo.primaryIndustry =3D
industries.industryId
WHERE (Users.UserID IN (SELECT DISTINCT UserID FROM vw_search ))
AND UserProfile.PropertyDefinitionID=3D29 AND
dbo.fnCommonFriendsStep(Users.UserID,36) >=3D0 ORDER By Degree ASC
fnCommonFriendstep one calculates relation****p between the loggedin
member and other members.
if exists (select * from dbo.sysobjects where id =3D object_id(N'[dbo].
[fnCommonFriendsStep]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnCommonFriendsStep]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnCommonFriendsStep
(
@[EMAIL PROTECTED]
INT,
@[EMAIL PROTECTED]
INT
)
RETURNS INT
AS
BEGIN
IF @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
0
DECLARE @[EMAIL PROTECTED]
TABLE (Generation INT, p INT)
DECLARE @[EMAIL PROTECTED]
INT
SELECT @[EMAIL PROTECTED]
=3D 0
INSERT @[EMAIL PROTECTED]
(
Generation,
p
)
SELECT 0,
memberId
FROM network
WHERE friendId =3D @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
NOT EXISTS (SELECT 1 FROM @[EMAIL PROTECTED]
WHERE p =3D @[EMAIL PROTECTED]
) AND
@[EMAIL PROTECTED]
>=3D 0
BEGIN
SELECT @[EMAIL PROTECTED]
=3D @[EMAIL PROTECTED]
+ 1
INSERT @[EMAIL PROTECTED]
(
Generation,
p
)
SELECT @[EMAIL PROTECTED]
IN (SELECT p FROM @[EMAIL PROTECTED]
WHERE Generation =3D
@[EMAIL PROTECTED]
- 1)
AND memberId NOT IN (SELECT p FROM @[EMAIL PROTECTED]
)
UNION
SELECT @[EMAIL PROTECTED]
IN (SELECT p FROM @[EMAIL PROTECTED]
WHERE Generation =3D
@[EMAIL PROTECTED]
- 1)
AND friendId NOT IN (SELECT p FROM @[EMAIL PROTECTED]
)
IF @[EMAIL PROTECTED]
=3D 0
SELECT @[EMAIL PROTECTED]
=3D -2
END
RETURN @[EMAIL PROTECTED]
+ 1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GetRecommendations get the number of recommendations a member has
received.
if exists (select * from dbo.sysobjects where id =3D object_id(N'[dbo].
[GetRecommendations]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetRecommendations]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.GetRecommendations
(
@[EMAIL PROTECTED]
as int
)
RETURNS INT
AS
BEGIN
DECLARE @[EMAIL PROTECTED]
INT
SELECT @[EMAIL PROTECTED]
(*) FROM endorsements WHERE memberId=3D@[EMAIL PROTECTED]
AND
status=3D'Accepted'
RETURN @[EMAIL PROTECTED]
QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GetConnectionsCount fetches number of connections a member has
if exists (select * from dbo.sysobjects where id =3D object_id(N'[dbo].
[GetConnectionsCount]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetConnectionsCount]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.GetConnectionsCount
(
@[EMAIL PROTECTED]
as int
)
RETURNS INT
AS
BEGIN
DECLARE @[EMAIL PROTECTED]
INT
SELECT @[EMAIL PROTECTED]
(*) FROM network WHERE (memberId=3D@[EMAIL PROTECTED]
OR
friendId=3D@[EMAIL PROTECTED]
) AND status=3D1
RETURN @[EMAIL PROTECTED]
QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Please do let me know if you need any more information.
|
