Limiting results to closest one of each type using datamapper

[eluser]Andy78[/eluser]
Ok I have a search function that returns a list of takeaways based on a postcode radius search as the code shows below.

But I want to limit the results to the closest one takeaway of each type. There are 4 types of takeaway based on a takeaway_type_id field. So if the user enters their own postcode a single takeaway of each type will be presented to them which is also the closest takeaway of that type to the entered postcode.

Whats the best way to do this with datamapper? I know I should move this existing logic into the model rather than the controller but Ill wait till logic is all working.

Code:

function search(){
        
         //get the user's postcode
        $postcode = $this->input->post('postcode');
        //make sure post upper case and has no spaces
        $location = strip_tags($postcode);
     $location = strtoupper($postcode);
     $location = str_replace(" ", "", $postcode);
        $d = 10;
        
        //get all postcodes in a 10 mile radius of a postcode-location sorted by distance
        $postlist = $this->geozip->get_zips_in_range($location, 10, SORT_BY_ZIP_ASC, TRUE);
        
        //Get footer menu items
        $footer_menu = new Menu_item();
        $footer_menu->where('menu_id', 2);
        $footer_menu->where('published', 1);//make sure item is published
        $footer_menu->order_by('order', 'asc')->get();
        
        $takeaway = new Takeaway();
        //$list = $takeaway->get_takeaways($postlist);
        $takeaway->select('id, name, address, postcode, distance, mile_cost, pizza, chinese, indian, kebab'); //Select only the required fields
        $takeaway->where_in('postcode', $postlist);
        $takeaway->get();
        
        
        $distance = $this->geozip->get_distance($location, $takeaway->postcode);//get the distance between the two postcodes
        
        $content_data['footer_menu'] = $footer_menu;
        $data['footer_m'] = $this->load->view('footer_menu', $content_data, TRUE); //pull in footer menu view
        $data['location'] = $location;
        $data['takeaway'] = $takeaway;
        
        $this->load->view('results', $data);
    }

[eluser]WanWizard[/eluser]
You don't have the distance in the database, so I don't immediately see how Datamapper can solve this for you.

[eluser]Andy78[/eluser]
Could I not just pass the distance in as param to be used within a query? It does not all have to be done in a single query I am just trying to figure how the most efficient way of doing it.

[eluser]WanWizard[/eluser]
Assuming the $postlist is in order of distance, you could probably generate a SELECT CASE, where you compare the selected id with the value in the array assign an index value to a custom column you can order on.

Something like

Code:

// assume this is the array
$postlist = array( 0 => 12, 1 => 33, 2 => 6);

$case = 'index AS SELECT CASE `id`';

foreach ($postlist as $key => $value)
{
   $case .= ' WHEN '.$value.' THEN '.$key;
}
$case .= ' ELSE 0';

Just append it to the SELECT, and order by index ASC.

[eluser]Andy78[/eluser]
Ill give that a try.

[eluser]Andy78[/eluser]
I am still a bit lost with this TBH How would I apply what you suggest to my code..

My array contains a list of postcodes and calculated distances sorted by DISTANCE_ASC Like so:

FK27DJ => 0.094146570284875

echoing is out:

FK27DR is 0 miles away from the origin.
FK27DT is 0.020842665985334
FK27DY is 0.041726605820535
FK27DH is 0.059663467517565
FK27DS is 0.061726390730188
FK27DW is 0.063290442935439
FK27DP is 0.065667715550583

[eluser]Andy78[/eluser]
does the Where_in clause use the key or value of the array?

[eluser]WanWizard[/eluser]
where_in() needs an indexed array of values.

So in your case you need to pass array_keys() because your array is an assoc array in which the keys are the postcodes you need.

Your CASE statement needs to generate something like

Code:

SELECT CASE `postcode` WHEN 'FK27DR' THEN 0 WHEN 'FK27DT' THEN 1 WHEN 'FK27DY' THEN 2 END AS `index` // etc...

if you then order the result by index, the closest will be first.

I used integers here because they sort quicker, but if you prefer you can use the actual distance here.