recursive category count
ActivePublic
Actions

Authored by stwalkerster on Apr 8 2016, 5:46 PM.

Tags

None

Referenced Files

	F307: recursive category count
	Apr 8 2016, 7:14 PM

	F305: recursive category count
	Apr 8 2016, 5:46 PM

Subscribers

None

	-- create a temporary table to hold page IDs for results
	create temporary table if NOT EXISTS u2204__testing.pageid ( id int(11) PRIMARY KEY );

	-- enable recursion
	set MAX_SP_RECURSION_DEPTH = 255;

	-- create a function to do the work
	DROP PROCEDURE IF EXISTS u2204__testing.categoryCount;
	DELIMITER ';;'
	CREATE PROCEDURE u2204__testing.categoryCount(in categoryname varchar(255)) BEGIN
	-- define some variables I'm going to use
	declare pid int;
	declare pns int;
	declare ptitle VARCHAR(255);
	declare done int default false;

	-- declare a cursor (think a SQL statement that gets executed into a set of results, which you can then loop over)
	declare cursor_i cursor for select page_id, page_namespace, page_title from enwiki_p.categorylinks
	inner join enwiki_p.page on page_id = cl_from
	where cl_to = categoryname;
	declare continue HANDLER for not found set done = true;

	-- OK, we're ready to start. Open the cursor to execute the query
	open cursor_i;

	-- loop over the results in the cursor
	read_loop: LOOP
	-- get the current row and put the results into the three variables
	FETCH cursor_i INTO pid, pns, ptitle;

	-- done is set above when there's no more results, so quit the loop
	if done THEN
	LEAVE read_loop;
	END IF;

	-- check the page namespace is 14, and if it is, call this procedure again with the new category title
	if pns = 14 THEN
	call u2204__testing.categoryCount(ptitle);
	END IF;

	-- insert this page's ID into the results table (doing a dummy operation if the key already exists)
	insert into u2204__testing.pageid values (pid) on DUPLICATE KEY UPDATE id = id;

	END LOOP;

	-- we're done with the cursor, so close it
	close cursor_i;

	-- end of the stored procedure declaration
	END;;
	DELIMITER ';'

	-- call the stored procedure we just defined
	CALL u2204__testing.categoryCount('Philosophy');

	-- simple count from the results.
	select count(*) from u2204__testing.pageid;

Event Timeline

stwalkerster created this paste.Apr 8 2016, 5:46 PM

stwalkerster created this object with visibility "Public (No Login Required)".

stwalkerster edited the content of this paste. (Show Details)Apr 8 2016, 7:14 PM

• import-bot changed the edit policy from "All Users" to "Community (Project)".Feb 3 2017, 3:03 PM

recursive category countActivePublicActions

Event Timeline

recursive category count
ActivePublic
Actions