Know who your friends are with Tweet-SQL

Posted on May 1, 2009, 12:49 pm, by Rhys, under Tweet-SQL.

Tweet-SQL version 2 supports the new Twitter social graph API methods. These are two very simple methods to get all of your followers, or friends, Twitter user ids. There are four procedures in Tweet-SQL that support these methods.

  • tweet_sog_followers – Returns the ids of the authenticating users followers or, if a non-null value is supplied for @user_id_or_nick, the ids of the specified user’s followers.
  • tweet_sog_friends – Returns the ids of the authenticating users friends or, if a non-null value is supplied for @user_id_or_nick, the ids of the specified user’s friends.
  • tweet_sog_followersTable – Returns the same data from tweet_sog_followers but saves it to a table in the SQL Server database Tweet-SQL is hosted in. The table created is called tweet_followersIds. Any existing table with this name is dropped at the start of the procedure execution.
  • tweet_sog_friendsTable – Returns the same data from tweet_sog_friends but saves it to a table in the SQL Server database Tweet-SQL is hosted in. The table created is called tweet_friendsIds. Any existing table with this name is dropped at the start of the procedure execution.

Twitter added these new methods to help out application developers that were finding the API limits too restrictive.

Executing the below T-SQL will fetch all the ids of the people you follow;

?View Code TSQL
-- The ids of who you follow
EXEC dbo.tweet_sog_friendsTable null;

A new table will be created in the same database.

?View Code TSQL
SELECT * FROM tweet_friendsIds;

image thumb Know who your friends are with Tweet SQL

Getting the list of ids for the Twitter users is just as easy;

?View Code TSQL
-- The ids of all your followers
EXEC dbo.tweet_sog_followersTable null;

This data can be found in a table called tweet_followersTable

?View Code TSQL
SELECT * FROM tweet_followersIds;

image thumb1 Know who your friends are with Tweet SQL

Now we have this data it’s easy to find out the users we’re not following back.

?View Code TSQL
-- People you're not following back
SELECT *
FROM dbo.tweet_followersIds
EXCEPT
SELECT *
FROM dbo.tweet_friendsIds;

People you follow and who follow you back.

?View Code TSQL
-- Twitter buddies! You guys should have a beer!
SELECT *
FROM dbo.tweet_followersIds
INTERSECT 
SELECT *
FROM dbo.tweet_friendsIds

and people who don’t follow you back…

?View Code TSQL
-- Who's not following you back?
SELECT *
FROM dbo.tweet_friendsIds
EXCEPT
SELECT *
FROM dbo.tweet_followersIds;

If you really must know the names, of the people who aren’t following back, then run this T-SQL script. Just don’t react like Mark Corrigan would.

?View Code TSQL
-- Really need to know the names of the 
-- people who aren't following you back?
 
-- Insert the people who aren't following
-- you into a new table called NoFollows
SELECT *
INTO NoFollows
FROM dbo.tweet_friendsIds
EXCEPT
SELECT *
FROM dbo.tweet_followersIds;
 
-- Add PK
ALTER TABLE NoFollows ADD CONSTRAINT 
	pk_id PRIMARY KEY CLUSTERED
	(
		Id
	)
	ON [PRIMARY];
 
-- Add a column to hold Twitter name & screen name
ALTER TABLE NoFollows ADD name VARCHAR(50), screen_name VARCHAR(50);
 
-- Create a cursor to cycle through the users
DECLARE twitterCursor CURSOR STATIC FOR SELECT Id
					FROM dbo.NoFollows
					WHERE name IS NULL;
 
DECLARE @id INT, @name VARCHAR(50), @screen_name VARCHAR(50),
	@xml XML, @handle INT;
 
-- Turn of resultsets from Tweet-SQL
EXEC dbo.tweet_cfg_resultset_send 0;
 
-- Open the cursor and get the first result
OPEN twitterCursor;
FETCH NEXT FROM twitterCursor INTO @Id;
 
-- Loop!
WHILE (@@FETCH_STATUS = 0)
BEGIN
 
	-- Get the user information
	EXEC dbo.tweet_usr_show @Id, null, @xml OUTPUT;
	-- Prepare an xml document
	EXEC sp_xml_preparedocument @handle OUTPUT, @xml;
 
	SELECT @name = name, 
		   @screen_name = screen_name
	FROM OPENXML (@handle, '/user', 2)
	WITH
	(
		name VARCHAR(50),
		screen_name VARCHAR(50)
	);
 
	-- Update the table with the retrieved details
	UPDATE dbo.NoFollows
	SET name = @name,
	screen_name = @screen_name
	WHERE Id = @id;
 
	-- destroy the xml document
	EXEC sp_xml_removedocument @handle;
 
	-- Get the next row
	FETCH NEXT FROM twitterCursor INTO @Id;
 
	-- Be nice to Twitter Servers!
	WAITFOR DELAY '00:00:05';
 
END -- EOF While Loop
 
-- Clean up!
CLOSE twitterCursor;
DEALLOCATE twitterCursor;
-- Turn resultsets from Tweet-SQL back on as appropriate
EXEC dbo.tweet_cfg_resultset_send 1;
?View Code TSQL
SELECT *
FROM dbo.NoFollows;
Tags: , , ,
Comment (RSS)  |  Trackback

Leave a Reply

«
»