0

I'm facing MySQL query problem with ordering grouped rows.

Tables:

statuses (
  id int primary key auto_increment,
  name varchar(30)
);
orders (
  id int primary key auto_increment,
  order_code varchar(10),
  order_date timestamp default current_timestamp,
  user_id int -- fk to users
);
orders_statuses (
  id int primary key auto_increment,
  status_changed timestamp default current_timestamp,
  order_id int, -- fk to orders(id)
  status_id int -- fk to orders(id)
);

Now, I need to join them in ONE query (this is important due to database object limitation with my PHP software - no sub-queries allowed, not even union) and get all possible orders, but only the latest status change as current status.

Statuses from status table may be inserted randomly - there is no way to use max function on status_id field where order_id (currently id 1 is new, 2 is canceled, 4 is paid etc.).

Grouping by order_id (id in orders or order_code) always returns first available status inserted into orders_statuses, even if ordered by date.

I've also tried using having status_changed=max(status_changed), but it won't work.

So my question is: How should I group them by order_id to get last, not first status name available for each row?

// edit

Here is the code I'm trying to move from my Workbench to PHP DB Object:

select 
    (select l.name from statuses l where l.id = (select t.status_id from orders_statuses t where t.order_id = oh.id order by t.status_changed desc limit 1)) as status,
    oh.order_code, 
    oh.id, 
    oh.order_total, 
    su.name as order_user, 
    oh.order_date as ordered, 
    max(os.status_changed) as updated, 
    oh.id as uid 
from orders oh 
left join orders_statuses os on (oh.id = os.order_id) 
left join users su on (oh.shop_user_id = su.id) 
group by 
    oh.id 
order by 
    oh.order_date desc, 
    os.created desc

So basically I need simpler query to understand it and put it into $this->db->use('table', on condition)->...->select(array(fields))

// edit

After hours of thinking I can tell I'm lacking know-how. This problem can be solved by using 

SELECT
    status_id,
    created,
    order_id
FROM
    orders_statuses
GROUP BY
    order_id,
    status_id
ORDER BY
    created DESC;

and transforming it to return just one status_id for each order_id with max created within single order_id group - this is where I need your help.

Faisal
  • 4,591
  • 3
  • 40
  • 49
Michał
  • 1
  • 3
  • My current query looks like this: `select (select l.name from statuses l where l.id = (select t.status_id from orders_statuses t where t.order_id = oh.id order by t.created desc limit 1)) as status, oh.order_code, oh.id, oh.order_total, su.name as order_user, oh.order_date as ordered, max(os.created) as updated, oh.id as uid from orders oh left join orders_statuses os on (oh.id = os.order_id) left join users su on (oh.shop_user_id = su.id) group by oh.id order by oh.order_date desc, os.created desc` which is slow, I need something better and faster (via workbench only) – Michał Dec 11 '16 at 22:40
  • Add that to the original post instead, where it will be more readable. Please. – Petter Hesselberg Dec 11 '16 at 22:48
  • Done. @PetterHesselberg – Michał Dec 11 '16 at 23:09
  • What you are looking for is a *groupwise maximum*. Have a look at e.g. [Groupwise maximum](http://stackoverflow.com/questions/15211479/groupwise-maximum) or the [MySQL Documentation](http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html). For faster (more complicated) versions, see .e.g. [Groupwise Max in MySQL](http://mysql.rjweb.org/doc.php/groupwise_max). To your php-limitation: basically every framework supports some form of `raw` (e.g. `$this->db->raw('whatever query you like')`), you should give it a try (it's just not db-independent anymore then). Or use a view. – Solarflare Dec 12 '16 at 10:02
  • My point is, I need to get newest order status for each order. Strictly speaking I need status_id matching with max(status_changed) in group of each order_id. Group by order_id having status_changed=max(status_changed) - returns first inserted status_id. What I need is last one. – Michał Dec 12 '16 at 10:20

0 Answers0