Sort table result based on result of last used in another table

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.

I do not think you understood everything. I know how to just do a normal sort_by, but how to do that based on content from another table is my question. :) — moiamserru, Dec 04 '18 at 14:36
@moiamserru: I'm not sure about how you implement this in codeigniter - but the general MySQL way is to combine both tables in one query using a JOIN statement combined with a Sort By. You do need to ensure that both IDs refer to eachother. More details here probably: https://stackoverflow.com/questions/15200548/codeigniter-join-2-table-data — T. Altena, Dec 04 '18 at 15:36
If we skip the Codeigniter then. How would such a statement look like in my example? I understand the practice of join, but cannot see how I would make it so it works the way I need it to. — moiamserru, Dec 04 '18 at 15:56
So in plain MySQL: `Select * from customers join reports on customers.name = reports.name order by reports.date DESC;` Or am I missing something here? — T. Altena, Dec 04 '18 at 16:13
@T.Altena Thanks! Took me closer to the solution, but not quite. Your example gives me the customers name printed each time it comes across it on a report. I only weant it to print out once. Solved that with grouped by, but now I get the issues I updated my issue with up top. — moiamserru, Dec 05 '18 at 18:39
You could give this a go (in SQL): `Select * from customers join reports on customers.name = reports.name GROUP BY customers.name ORDER BY Max(reports.date) DESC;` But maybe you need to use a subquery for this as per: https://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by — T. Altena, Dec 06 '18 at 07:56

score 0 · Answer 1 · answered Feb 05 '19 at 16:03

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.

If this "might not be an answer to the question", as you say, then this might not be the right place to post it. If it does not answer this question, I would suggest that you also ask a separate question, answer it yourself and link to this question for reference. Also, everybody likes `code`. Please add some to better explain what you mean. — Adrian W, Feb 05 '19 at 16:54

Sort table result based on result of last used in another table

1 Answers1