Trying to assign a "rule" based on locations - any ideas for how the logic would work?

[eluser]matula[/eluser]
The project I'm working on is this: A package is shipped from Los Angeles to Tokyo. We have couriers who service various locations, and we need to figure out which courier is assigned to which package based on the package's pick-up and drop-off locations.

Here's the "package" table (pu = pickup, do = dropoff):

Code:

+ id + pu_city + pu_state + pu_country + do_city + do_state + do_country +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ 1  +   LA    +   CA     +    US      + Tokyo   +          +  JP        +
+ 2  + London  +          +    UK      + Sydney  +          +  AU        +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Then I have a "rules" table:

Code:

+ courier_id + pu_type + pu_country + pu_location + do_type + do_country + do_location +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+     1      +  city   +   US       +   LA        + country +   JP       +             +
+     2      + country +   US       +             + country +   JP       +             +
+     3      + country +   US       +             + region  +            +      1      +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

"Region" is just a table of paired values, with a region_id and country_id

Code:

+ region_id  + country_id +
+++++++++++++++++++++++++++
+     1      +   JP       +
+     1      +   CN       +
+     2      +   US       +
+     2      +   CA       +

And Region 1 would be the "Asia" region, Region 2 the "North America" region.

What I'm trying to do is find a rule that matches the locations of the package. In my scenario, it would be the first rule. If the package were Boise, Idaho to Osaka, Japan... it would be the second rule.

Right now, I'm doing multiple loops to find a match, like this:

Code:

$this->db->select('courier_id')->from('rules')->where('pu_type','city');
$this->db->where('pu_country',$pu_country)->where('pu_location', $pu_city);

if THAT doesn't find a match, I loop thru the countries...

Code:

$this->db->select('courier_id')->from('rules')->where('pu_type','country');
$this->db->where('pu_country',$pu_country);

if THAT doesn't find anything, I loop thru the regions

Code:

$this->db->select('r.courier_id')->from('rules r');
$this->db->join('regions re','re.region_id = r.pu_location')->where('r.pu_type','region');
$this->db->where('re.country_id',$pu_country)->where('r.pu_location','re.region_id);

And that's just the PICKUPS. Once I get the ids for that, I then loop back thru to find a match for the dropoff rules... and if there's a match, I have the courier I need.

As you can image, this gets super long and convoluted to code. Has anyone ever had a project like this, and can offer some advice? The schema is not set in stone, so if I need to start from the beginning, that's an option.

Thanks for any help!

[eluser]Nick_MyShuitings[/eluser]
From the way you presented the problem, the logic of performing the three tiered search is somewhat unavoidable. But... you can make your life easier by taking advantage of separate functions.

So, you'd have a model... called courier_model.php for example. In that model you have one function called find_courier(); That function would call three other functions in order something like this:

Code:

public function find_courier($city, $state, $country) {
  if (FALSE !== $result = $this->first_level($city,$state,$country)) {
    return $result;
  } elseif (FALSE !== $result = $this->second_level($city,$state,$country)) {
    return $result;
  } else (FALSE !== $result = $this->third_level($city,$state,$country)) {
    return $result;
  }
}

...
private function first_level() {
... do your sql here... I suggest avoiding AR, it is useless, use actual SQL with query bindings...
}

...

That should keep your code clean and less convoluted, and it works the same for pickup and drop off... warning untested variable assignment in the if clause... but that'd save a few lines.