根据最后在另一个表中使用的结果对表结果进行排序

Question

I have a page on my site that prints out all the customer names and id's in the database for use in a report to another database on work done.

What I would like to do is for this function to sort customers so that I get that list sorted in the order of when the customer was last used for in report. Where the newest report would make that reports customer land on first place in my result customer.

I am using Codeigniter and my controller has this code:

$data['reports'] = $this->db->get('reports');

$data['customers'] = $this->db->get('customers');

$this->load->view('list', $data);

That add this in my view:

foreach($customers->result() as $customer){ 
   echo "<option value='".$customer->id."'>".$customer->name."</option>";
}

I added a picture that hopefully makes this easier to understand.

UPDATE #1:

So I have with help in the comments below worked out this:

Controller:

$this->db->group_by('customer');
$this->db->order_by('date', DESC);
$data['reports'] = $this->db->get('reports');

$data['customers'] = $this->db->get('customers');

$this->load->view('list', $data);

View:

foreach($reports->result() as $rep){ 
    foreach($customers->result() as $customer){
        if($rep->customer == $customer->id){
            echo "<option value='".$customer->id."'>".$customer->name."</option>";
        }
    }
}

This gives me the customer echoed one time in the order of report date.

HOWEVER! This is not working as I intended it to. Because, yes, it sorts in order of the date, but it has grouped the reports first so it takes the date that comes last and present in the echo. Which will be the first date ever recorded for that customer. The opposite of what I wanted.

I have not figured out a way to change in what order grouped by is processed. If that is even possible..

Also it does not pick up any customer that does not have a report.

Answer 1

It might not be an answer to the question, but it made my project work at least. Instead of using a SQL command to pick out the last date from reports and combine it with the customer list I started in the other end with a new approach.

I had another project where I needed to sort posts on a site and there I set the posts to a specific sorting value each time they moved.

I used a variant of this and inserted it on this issue. So now when I do a report I also insert the current timestamp into "last_report" on the customer so now when I check the customer list I don't even need to call the reports, but only sort by the last_report which is able to be ordered with ASC and DESC.

This might be a bad use of database, but it works and the total counts of database questions through a complete cycle is still the same.