Re: Recursive Search

[Shawn Green]

Peter Brawley wrote:
> 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.
>

I agree with the need to script the recursion (whether it's in PHP or an a stored procedure) but I think the dynamic view method is a bit of overkill.

Basically, you need to create a table (I would prefer it to be TEMPORARY, but that is up to you) with the first generation of information then add to that table successive generations until you reach the number of iterations you want to find.

CREATE TEMPORARY TABLE tmpFriendsList (
mem_id bigint,
frd_id bigint,
UNIQUE(frd_id)
)

-- first generation
INSERT IGNORE tmpFriendsList(mem_id, frd_id)
SELECT mem_id, frd_ID
FROM members
WHERE mem_id = 1;

--successive generations
INSERT IGNORE tmpFriendsList (mem_id, frd_id)
SELECT tf.frd_id, m.frd_id
FROM tmpFriendsList tf
LEFT JOIN members m
ON m.mem_id = tf.frd_id;


The UNIQUE key will prevent adding the same friend more than once. The IGNORE will allow you to keep going even if you run into a duplicate friend. Repeat the second query as often as needed to build each generations after the first.

The last things to do are to get the list of friends you just built and clean up after ourselves:

SELECT frd_id
FROM tmpFriendsList;

DROP TEMPORARY TABLE tmpFriendsList;

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine