From PHP how to efficiently execute this search in SQL Server?

Question

I have a database structure something like the following:

Table A: PersonId, GroupId
Table B: GroupId, ParentGroupId

Given a PersonId, I want to find the Ids of all people in parent groups of that person's group.

First I select the ParentGroupId for the given PersonId, by joining with B. Then I do a while loop, selecting and recording the PersonId from A based on the GroupId returned in the previous search, and continue the loop by obtaining the next ParentGroupId from B.

Is this an efficient way to do this search, or is there an option that does not involve a while to "bubble up" in this manner?

(this is a simplified version of the actual scenario, changing the schema is not an option)

 $sql = 'SELECT ParentGroupID FROM A WHERE PersonId = ' . $id;
  $result = $db->query($sql);
  $row = $db->fetch_array($result); 
  $parent_group = $row['ParentGroupId'];

  if(!is_null($parent_group)) {

      $parent_ids = array();

      while($parent_group > 0) {
          //is there a way to do this where I retrieve all managers <= lvl 6 at once, so I don't have to loop in order to 'tier up'?
          $sql = 'SELECT ParentGroupID, PersonID
                  FROM B 
                  INNER JOIN A on ParentGroupID = A.GroupID 
                  WHERE  ParentGroupID = ' . $parent_group;

          $result = $db->query($sql);
          $row = $db->fetch_array($result); 
          $parent_group = $row['ParentGroupID'];
          $parent_ids[] = $row['PersonID'];
      }
  }

@Mihai doing another query for each pass through a loop is the definition of inefficient. — Sean Lange
– Sean Lange, Commented May 4, 2015 at 16:27
What you could do is compress the 2 queries into 1 with a LEFT or RIGHT JOIN and then check your values with WHERE IS NULL — Mihai
– Mihai, Commented May 4, 2015 at 16:30
The while loop will iterate as long as parent groups exist for the previous obtained ParentGroupID — ab11
– ab11, Commented May 4, 2015 at 16:32

Digital Chris · Accepted Answer · 2015-05-04 16:34:05Z

1

Combining your two queries into one would be more efficient:

$sql = 'SELECT ParentGroupID, PersonID
  FROM B 
  INNER JOIN A on ParentGroupID = A.GroupID 
  WHERE  ParentGroupID IN (
    SELECT ParentGroupID FROM A WHERE ParentGroupID > 0
    AND PersonId = ' . $id .')' ;

answered May 4, 2015 at 16:34

Digital Chris

6,1771 gold badge22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Digital Chris Over a year ago

@Don'tPanic His code doesn't seem to be recursive, it just gets all people with the same parent groups as a users (if I'm reading correctly). From the question: "Given a PersonId, I want to find the Ids of all people in parent groups of that person's group."

ab11 Over a year ago

Well, it 'recurses' up the parent group structure. A group has a parent, which has a parent, etc... and the code iterates, recording PersonIds, as long as a person's group has a parent.

Collectives™ on Stack Overflow

From PHP how to efficiently execute this search in SQL Server?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related