How can I write this MySQL query?

Question

We have a system where users send in bags of goods to us.

I am trying figure out how to pull users with a calculated field for the date they sent their 3rd bag of goods. The state on the history table that would indicate this event is "processed". If I wanted to just get the received date for a particular bag I would write:

SELECT bags.created_at FROM history 
WHERE history.state='received' AND history.bag_id LIMIT 1

Here are our tables:

users
  id

bags
  user_id

history
  state
  bag_id
  created_at

The end goal is a select query that gives us user records and adds the "field" 3rd_bag_received_at

Any ideas?

The `processed` event you indicate in your question would be stored in `history.state`? And `history.bag_id` references a `bags.id` (that you haven't listed in your schema)? — Sepster, Oct 12 '12 at 01:52
In concentrating on the logic to achieve this, I missed the point that you wanted to handle this as a computed column, sorry. I have updated my answer to reflect how to manage that. — Sepster, Oct 17 '12 at 23:51

score 3 · Answer 1 · edited May 23 '17 at 12:04

You could group by user_id, and then add a having clause to include only users with count(distinct bag_id) >= 3.

Then limit that to 3, and order by to get the latest date max(created_at)

Does that help?

Implementation would look something like this:

select 
 user.id, 
 max(history.created_at) as third_bag_received_at

from
(

 select 
   user.id, 
   max(history.created_at)

 from 
   users

   inner join bags
   on user.id = bags.user_id

   inner join history
   on bags.id = history.bag_id

 where history.state = 'processed'

 group by
   user.id

 having
   count(history.bag_id >= 3)

 limit 3

 order by 2 -- does this work in mySql? (It means "column 2" in MS SQL)
)

Limit 1

EDIT: How to address join/aggregation to other tables in a computed column:

Rather than adding this as a calculated column, I would probably create a view with whatever info you needed for this requirement, including the third_bag_received column based on this query.

But if you really did need to implement this as a calculated column on your table:

I'd recommend creating a user function that accepts your user.id as an input, and returns the value of third_bag_received_at (again, based on the logic of the above query).

Then call this function from your calculated column. Refer to @user10635's answer on this SO question.

score 0 · Answer 2 · answered Oct 12 '12 at 02:01

0

I'm just developing a little more Sepster answer to make it clear. The question is erratic as it doesn't say that the table bags has any field called bags.id, I had to make it up.

SELECT users.id, MAX(history.created_at) as 3rd_bag_received_at
FROM users LEFT JOIN bags ON (users.id=bags.user_id)
LEFT JOIN history ON(bags.id = history.bag_id)
WHERE history.state='received'
GROUP BY users.id
HAVING COUNT(history.bag_id) >= 3;

answered Oct 12 '12 at 02:01

Sign Show

121
4

1

:-) I'd just added a concrete implementation anyway. Couple of issues with this one though - `3rd_bag_recieved` won't be a valid name, I think because it starts with a number. Also, you're still not limiting to the 3rd bag (eg if there's 4 or more bags), so you'll actually get the _latest_ bag date, not the 3rd bag. And also `history.state` needs to be "processed" (I think!). – Sepster Oct 12 '12 at 02:02
Fixed the errors pointed out by Sepster. Cheers! SELECT users.id, SUBSTRING(CONCAT(history.created_at),19,29) as third_bag_received_at FROM users LEFT JOIN bags ON (users.id=bags.user_id) LEFT JOIN history ON(bags.id = history.bag_id) WHERE history.state='processed' GROUP BY users.id HAVING COUNT(history.bag_id) >= 3; – Sign Show Oct 12 '12 at 05:22
That `SUBSTRING(CONCAT(history.created_at),19,29)` is novel. Part of me really likes this because it's a simpler approach to getting the 3rd entry than mine. But less robust; If using this approach, particularly with dates/times, you need to be very mindful of the date format in use as that will screw with your string positions (perhaps some kind of explode-at-injected-delimiter pattern might be safer?). But IMHO I don't think we should resort to string operations _if_ there's a set-based database approach that'll cover it, for performance (and _pureist_!) reasons. But it _is_ clever! :-) – Sepster Oct 12 '12 at 14:32

How can I write this MySQL query?

2 Answers2