Page 1 of 1

Interesting Logic Puzzle

Posted: Thu Jul 01, 2010 2:05 am
by jraede
So I think I figured this out, but without having all the data in place to test it, I was wondering if anyone could either find a more graceful way of doing it, or give some insight as to whether it would work.

Basically I have 5 levels of listings in my mysql database, with the bottom 4 each having a "parent" value corresponding to the one above it. Think of it as a hierarchy, or better, a tree. Each child can have one parent, but each parent can have infinite children.

I also have a table called user_listing_relations, which stores, among other things, which users are admins of which listings. The user_listing_relations table has a column for "level", which corresponds to one of the 5 levels of listings. I have the permissions set up so if a user is an admin of a certain listing, he/she is also the admin for all of that listing's children.

I want to have a function that returns all listing IDs of a certain level that a user is an admin of. It would take two arguments, user_id and listing_level, and basically would iterate through all listing levels to get the listings that the user is a direct admin of, and then reiterate again to get the children, grandchildren, or great-grandchildren of those listings that correspond to the level in question.

(Note that get_results returns an array of the SQL query results in stdClass format)

Here's what I have so far, any ideas/critique would be great:

Code: Select all

public static function get_admin_listings($user, $level) {
	$ids = array();
	$results = array();
	$final = array();
	for($i=1;$i<=$level;$i++) {
		$query = $this->get_results("SELECT `ulr_listing` FROM `lm_user_listing_relations` WHERE `ulr_user`='$user' AND `ulr_level`='$i'");
		foreach($query as $result) {
			$ids[] = $result->ulr_listing;
		}
		
		for($child = $i + 1;$child <=$level;$child++) {
			$results = $ids;
			foreach($results as $result) {
                                unset($ids);
                                $ids = array();
				$children = $this->get_results("SELECT `l".$child."_id` AS `id` FROM `lm_listing_level$child` WHERE `l".$child."_parent`='$result'");
				foreach($children as $child_listing) {
					$ids[] = $child_listing->id;
				}
			}
		}					
	}
	$final = array_unique($ids);
	return $final;		
}

Re: Interesting Logic Puzzle

Posted: Fri Jul 02, 2010 10:30 am
by Jade
Recursion to the rescue. This will need tweaking but at least you'll get the idea:

Code: Select all

//you can still call this function like
//get_admin_listings($user, $level);

function get_admin_listings($user, $level, $parent= 0 /* the parent if any */)
{ 
      $ids = array();
      $final = array();

      //get all ids for the root level
      if ($parent)
         $sql ="SELECT `l".$level."_id` AS `id` FROM `lm_listing_level$level` WHERE `l".$parent."_parent`='$parent'";
      else
          $sql .= "SELECT `ulr_listing` FROM `lm_user_listing_relations WHERE `ulr_user`='$user' AND `ulr_level`<='$level'";
      
      $query = $this->get_results($sql);

      //break out of the recursion if you've hit an ID that doesn't have any children
      if (!mysql_num_rows($query))
             return; //return an empty array

      foreach($query as $result)
      {
               if($parent)
                   $ids[] = $result->id;
               else
                   $ids[] = $result->ulr_listing;
      }                                        

          //now look for children that belong to this parent
          foreach($ids as $parent)
               $final = array_merge($ids, get_admin_listings($user, $level, $parent));

          return array_unique($final); //return all the data concatenated together
}

Re: Interesting Logic Puzzle

Posted: Fri Jul 02, 2010 1:30 pm
by jraede
Ah, cool, didn't think of recurring the function. I made some changes to my original one and I think I got a working one as well that just doubles back on itself with for and foreach loops, but I think yours is a bit cleaner. Check it out.

Code: Select all

public static function get_admin_listings($user, $level) {
		global $lmdb;
		$ids = array();
		$results = array();
		$final = array();
		for($i=1;$i<=$level;$i++) {
			$query = $lmdb->get_results("SELECT `ulr_listing` FROM `lm_user_listing_relations` WHERE `ulr_user`='$user' AND `ulr_level`='$i'");
			
			unset($ids);
			$ids = array();
			
			/** $ids is all listings of level $i that user is admin of */
			foreach($query as $result) {
				$ids[] = $result->ulr_listing;
			}
			
			/** Iterate through all child levels until we get to level in question */
			for($child = $i + 1;$child <=$level;$child++) {
				
				$results = $ids;
				unset($ids);
				$ids = array();
				/** Start with parents, then get children, grandchildren, etc */
				foreach($results as $result) {
					$children = $lmdb->get_results("SELECT `l".$child."_id` AS `id` FROM `lm_listing_level$child` WHERE `l".$child."_parent`='$result'");
					
					/** Add children of this parent to ids array */
					foreach($children as $child_listing) {
						$ids[] = $child_listing->id;
					}
				}
				/** ^^ Take ids array of children, and run through it again, to get THEIR children (if we haven't already gotten to the level we want */
			}
			
			/** Add array of all child listings of level $level to the final array, and then go to the next listing level */
			$final = array_merge($final, $ids);				
		}
		$final = array_unique($final);
		return $final;		
	}