MySQL Forums
Forum List  »  General

Re: Recursive Search
Posted by: Peter Brawley
Date: October 20, 2005 08:04PM

There's no recursion in MySQL so you have to write this in an app language (as you have done) or in a stored procedure, like the following, which carries more info than you may need, and which may require some revision, but I hope it gives you a idea of how to proceed. The basic plan is to create a table (friendlist) to hold the result, seed it with first-order friends, then use a View to iteratively populate the friend list till there areno more entries to process.

DROP TABLE IF EXISTS friendlist;
CREATE TABLE friendlist (
mem_id INT NOT NULL,
frd_id INT NOT NULL,
path CHAR(50) default NULL
);

CREATE OR REPLACE VIEW nextfriends AS
SELECT
l.frd_id AS l_frd_id,
f.frd_id AS f_frd_id,
CONCAT(l.path,',',f.frd_id) AS newpath
FROM friendlist AS l
INNER JOIN friends AS f
ON l.frd_id = f.mem_id
AND LOCATE(f.frd_id,l.path)=0
WHERE NOT EXISTS (
SELECT NULL FROM friendlist AS l2
WHERE f.frd_id=l2.frd_id
)
GROUP BY l.frd_id,f.frd_id;

DROP PROCEDURE IF EXISTS BuildFriendList;
DELIMITER |
CREATE PROCEDURE BuildFriendList( IN who INT )
BEGIN
DECLARE rows INT DEFAULT 0;
CREATE TABLE IF NOT EXISTS friendlist (
mem_id INT NOT NULL,
frd_id INT NOT NULL,
path CHAR(50)
);
TRUNCATE friendlist;
-- Seed friends with immediate friendships
INSERT INTO friendlist
SELECT
mem_id,
frd_id,
CONCAT(mem_id,',',frd_id)
FROM friends
WHERE mem_id=who;
SET rows = ROW_COUNT();
WHILE (rows > 0) DO
-- Add friends till there are no more
INSERT INTO friendlist
SELECT * FROM friendlist;
SET rows = ROW_COUNT();
END WHILE;

END;
|
DELIMITER ;

Options: ReplyQuote


Subject
Written By
Posted
October 20, 2005 06:22AM
Re: Recursive Search
October 20, 2005 08:04PM
October 24, 2005 11:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.