From c50bd1cf600bc07608e6dfdf4f7c854ee13aee29 Mon Sep 17 00:00:00 2001 From: "Matteo Nastasi (mop)" Date: Mon, 21 Dec 2015 08:10:50 +0100 Subject: [PATCH] rewrite wrong implementation of party view --- sql/sql.d/075-users-network.sql | 41 ++++++++++++++++++++++----------- sql/sql.d/910-usernet_devel.sql | 6 ++--- 2 files changed, 31 insertions(+), 16 deletions(-) diff --git a/sql/sql.d/075-users-network.sql b/sql/sql.d/075-users-network.sql index a4a9958..c725938 100644 --- a/sql/sql.d/075-users-network.sql +++ b/sql/sql.d/075-users-network.sql @@ -47,19 +47,34 @@ CREATE VIEW #PFX#usersnet_wideskill DROP VIEW #PFX#usersnet_narrowskill; CREATE VIEW #PFX#usersnet_narrowskill - AS SELECT un.owner AS owner, ur.target AS target, SUM(ur.skill * un.trust) / SUM(un.trust) AS skill, - COUNT(*) AS count, - SUM(CASE WHEN ur.friend = 1 THEN 1 ELSE 0 END) AS black -- count number of friend - -- blacklisting the target - FROM #PFX#usersnet AS un, #PFX#usersnet as ur - WHERE un.target = ur.owner AND un.friend = 5 -- ur owner must be bbf of un.owner - GROUP BY un.owner, ur.target HAVING SUM(CASE WHEN ur.friend = 1 THEN 1 ELSE 0 END) = 0; + AS SELECT + un.owner AS owner + , ur.target AS target + , SUM(ur.skill * un.trust) / SUM(un.trust) AS skill + , COUNT(*) AS count + FROM #PFX#usersnet AS un, #PFX#usersnet as ur + WHERE un.target = ur.owner AND un.friend = 5 -- 'ur' owner must be bbf of un.owner + AND ur.friend > 2 + GROUP BY un.owner, ur.target; + +DROP VIEW #PFX#usersnet_narrowblack; +CREATE VIEW #PFX#usersnet_narrowblack + AS SELECT DISTINCT un.owner AS owner, ur.target AS target, 1 AS black_cnt + FROM #PFX#usersnet AS un, #PFX#usersnet as ur + WHERE un.target = ur.owner + AND un.friend = 5 -- ur owner must be bbf of un.owner + AND ur.friend = 1; -- ur must be blacked DROP VIEW #PFX#usersnet_party; CREATE VIEW #PFX#usersnet_party - AS (SELECT ns.* FROM #PFX#usersnet_narrowskill AS ns - -- all except targets managed directly by the owner - WHERE target NOT IN (SELECT target FROM #PFX#usersnet WHERE owner = ns.owner) - UNION ALL - (SELECT owner, target, skill, 1 AS count, 0 AS black FROM #PFX#usersnet - WHERE friend > 2)) ORDER BY target; + AS (SELECT ns.*, nb.black_cnt FROM #PFX#usersnet_narrowskill AS ns + LEFT JOIN #PFX#usersnet_narrowblack AS nb + USING (owner, target) + -- all except targets managed directly by the owner + WHERE black_cnt is null + AND target NOT IN (SELECT target FROM #PFX#usersnet AS du WHERE du.owner = ns.owner) + UNION ALL + (SELECT owner, target, skill, 1 AS count, null AS black_cnt + FROM #PFX#usersnet + WHERE friend > 2)) + ORDER BY target; diff --git a/sql/sql.d/910-usernet_devel.sql b/sql/sql.d/910-usernet_devel.sql index 9efb9f9..1fd6ce2 100644 --- a/sql/sql.d/910-usernet_devel.sql +++ b/sql/sql.d/910-usernet_devel.sql @@ -63,9 +63,9 @@ SELECT owner, target, skill, 1 AS count, us.login as login FROM #PFX#usersnet, SELECT ns.*, us.login AS login FROM #PFX#usersnet_narrowskill as ns, #PFX#users AS us WHERE owner = 10101 AND ns.target NOT IN (SELECT target FROM #PFX#usersnet WHERE owner = 10101) -AND us.code = ns.target UNION SELECT owner, target, skill, 1 AS count, 0 AS black, us.login as login FROM #PFX#usersnet, #PFX#users as us WHERE owner = 10101 AND us.code = target AND friend > 2 ORDER BY target; - -SELECT un.owner, ur.target, SUM(ur.skill * un.trust) AS num, SUM(un.trust) AS den, SUM(ur.skill * un.trust) / SUM(un.trust) AS skill, COUNT(*) AS count FROM #PFX#usersnet AS un, #PFX#usersnet as ur WHERE un.target = ur.owner AND un.friend = 5 AND ur.target NOT IN (SELECT target FROM #PFX#usersnet WHERE owner = un.owner) GROUP BY un.owner, ur.target; +AND us.code = ns.target UNION SELECT owner, target, skill, 1 AS count +-- , 0 AS black +, us.login as login FROM #PFX#usersnet, #PFX#users as us WHERE owner = 10101 AND us.code = target AND friend > 2 ORDER BY target; SELECT us.login, pa.* FROM #PFX#usersnet_party pa, #PFX#users as us WHERE pa.target = us.code AND pa.owner = 10101; -- 2.17.1