graph working 2.sql

USE tempdb
GO

/*
I have this N:M relationship:
How to select all usernames which are in the group "group1"?
*/

DROP TABLE IF EXISTS dbo.auth_user
DROP TABLE IF EXISTS dbo.auth_group
DROP TABLE IF EXISTS dbo.auth_user_groups
DROP TABLE IF EXISTS dbo.users
DROP TABLE IF EXISTS dbo.groups
DROP TABLE IF EXISTS dbo.hasGroup
GO

-- Original tables
CREATE TABLE dbo.auth_user (
    id integer NOT NULL,
    username character varying(150) NOT NULL
);

CREATE TABLE dbo. auth_group (
    id integer NOT NULL,
    name character varying(80) NOT NULL
);
CREATE TABLE dbo.auth_user_groups (
    id integer NOT NULL,
    user_id integer NOT NULL,
    group_id integer NOT NULL
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);
GO


-- Graph tables
CREATE TABLE dbo.users (
	userId		INT PRIMARY KEY,
	userName	VARCHAR(150)
	) AS NODE;

CREATE TABLE dbo.groups (
	groupId		INT PRIMARY KEY,
	groupName	VARCHAR(80)
	) AS NODE;

CREATE TABLE dbo.hasGroup AS EDGE;
GO

-- Populate graph tables from original tables
INSERT INTO dbo.users ( userId, userName ) 
SELECT id, username
FROM dbo.auth_user

INSERT INTO dbo.groups ( groupId, groupName ) 
SELECT id, name
FROM dbo.auth_group

-- Add the edge; user hasGroup group
INSERT INTO dbo.hasGroup ( $from_id, $to_id ) 
SELECT u.$node_id, g.$node_id
FROM dbo.auth_user_groups ug
	INNER JOIN dbo.users u ON ug.[user_id] = u.userId
	INNER JOIN dbo.groups g ON ug.group_id = g.groupId
GO


-- Queries
select *
from auth_user
where id in (select aug.user_id
             from auth_group ag
               join auth_user_groups aug on aug.group_id = ag.id
             where ag.name = 'group1');


-- Graph query
SELECT FORMATMESSAGE ( 'User %s (%i) is in group %s.', [user].userName, [user].userId, [group].groupName ) msg
FROM dbo.users [user], dbo.hasGroup hasGroup, dbo.groups [group]
WHERE [group].groupName = 'group1'
  AND MATCH ( [user]-(hasGroup)->[group] );