Monks,

My mind is in a bend over what I thought was a simple problem to solve. After many hours of struggling, I am coming to you for light --

I have simple schema

CREATE TABLE node (node_id, <bunch of other node related columns>);
CREATE TABLE edge (edge_id, from_node_id, to_node_id, <bunch of other 
+edge related columns>);
[download]

Note: In spite of edges being terminated in from_node and to_node, they are really not directed.

Given a node_id, I want all the edges that node_id appears in. So far so good. I've got this solved. The problem happens in implementing a limit clause with a difference. I want to be able to limit the number of degrees away from the initial node the query recurses into. In other words, if node 1 is connected to nodes 2 and 3, and 3 is connected to 4 and 5, but if I say limit=1 then only the edges (1,2) and (1,3) are retrieved. If I say limit=2, then besides the above two edges, (3,4) and (3,5) are also retrieved.

I've got some non-working code below. My problem is tracking which iteration I am on.

sub _edges {
  my (%args) = @_;
  
  my $node_id = $args{node_id};
  my $limit   = $args{limit} || 1;
  my $edges   = $args{edges} || [];
  my $iter    = $args{iter}  || 1;
  my $seen    = $args{seen}  || [];
  
  
  if ($iter > $limit) {
    return $edges;
  }

  
    my $sth = $dbh->prepare(qq{
      SELECT e.edge_id, e.to_node_id AS node_id 
      FROM edge e JOIN node n ON e.to_node_id = n.node_id 
      WHERE e.from_node_id = ?
        UNION 
      SELECT e.edge_id, e.from_node_id AS node_id 
      FROM edge e JOIN node n ON e.from_node_id = n.node_id 
      WHERE e.to_node_id = ?
    });
    $sth->execute($node_id, $node_id);
     
    my $res = $sth->fetchall_arrayref({});
    
    push(@$edges, @$res);
    
    foreach my $e (@$res) {
      unless (_seen($seen, [$node_id, $e->{node_id}])) {
        push @$seen, [$node_id, $e->{node_id}];
        _edges(
          dbh        => $dbh, 
          node_id    => $e->{node_id}, 
          node_name  => $e->{node_name}, 
          limit      => $limit, 
          abbreviate => $abbreviate, 
          iter       => $iter, 
          edges      => $edges, 
          seen       => $seen
        );
      }
    }
    $iter++;
  
  return $edges;
}

sub _seen {
  my ($i, $j) = @_;
  
  for (@$i) {
    return 1 if (
      (($_->[0] == $j->[0]) && ($_->[1] == $j->[1])) || 
      (($_->[0] == $j->[1]) && ($_->[1] == $j->[0]))
    );
  }
  
  return 0;
}
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.