MySQL Select where in array of statuses and last occurrence equals specific value

Question

Let's say I have a table like this:

id  user_id status  updateded_id
1   1       yes     2/5/2013
2   2       no      2/1/2013
3   3       yes     1/28/2013
4   2       yes     1/24/2013
5   2       no      1/20/2013
6   1       yes     1/16/2013
7   3       no      1/12/2013
8   4       yes     1/8/2013
9   5       yes     1/4/2013
10  5       no      12/31/2012
11  6       yes     12/27/2012
12  7       no      12/23/2012
13  6       yes     12/19/2012
14  4       yes     12/15/2012
15  3       no      12/11/2012
16  3       yes     12/7/2012
17  1       no      12/3/2012
18  1       yes     11/29/2012

I need to write a query that selects a user_id only if the status at the latest updateded_id is 'yes`.

For example user_id 1 will be choosen and user_id 2 won't. I write this in a complex Larvel query builder (Larvel version 4.2).

The result is multiple users. So in this query user is should be selected only once and only if the last occurrence in statuses table be yes.

I tried:

    $query->join('mytable', function ($join) use ($statuses) {
            ->whereRaw('mytable.status in ('.$statuses.') )
            ->orderBy('mytable.updated_at', 'desc')
            ->limit(1);
    });

AND:

    $query->join('statuses', function ($join) use ($statuses) {
            ->whereIn('mytable.status ,$statuses )
            ->orderBy('mytable.updated_at', 'desc')
            ->limit(1);
    });

Got error on whereIn and whereRaw function. Like they don'y exist.

So, Progress. Whit this query I can find one user that has the has 'yes' as the newest status. I can not find a way to make it choose all the user by this logic:

SELECT  s1.user_id 
FROM statuses s1
WHERE s1.user_id  = ( SELECT user_id  
                   FROM statuses s2
                   WHERE s2.status IN ('yes', 'no') ORDER BY s2.updateded_id DESC LIMIT 1) 
                   AND s1.status= 'yes';

Added, there were some other combinations that I already discarded. — matisa, Mar 29 '22 at 18:09
@RickJames Yes. This brings me closer. The complexity is that I can not for some reason user raw query in the join and also I need to select the user only if the latest between 2 statuses is equals 1. I think I'll need to mix the groupwise-maximum with if. — matisa, Apr 04 '22 at 07:55

sithuaung · Answer 1 · 2022-04-05T11:35:19.387

You could do this.

A user has multiple statuses and you want to get the users based on the latest status right?

Eloquent user model

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable;

    /*
     * Get the user's statuses
     */
    public function statuses()
    {
        return $this->hasMany(Status::class);
    }

    /**
     * Get the user's latest status.
     */
    public function latestStatus()
    {
        return $this->hasOne(Status::class)->latestOfMany('updated_at');
    }
}

Then you can write the query like this

return User::whereHas('latestStatus', function($query) {
    $query->where('status', 'yes');
})->get();

score 2 · Answer 2 · answered Apr 05 '22 at 17:42

I believe you're looking for what I call a "filter join." I'm not familiar with Laravel, so please accept MySQL:

SELECT t.user_id FROM table AS t
LEFT JOIN table AS filter ON (
  filter.user_id = t.user_id
  AND filter.updated_id > l.updated_id
)
WHERE filter.id IS NULL
AND t.status = 'yes';

Using the data you provided in the question, my output was:

I believe that satisfies the requirements of "users that have 'yes' as their newest status."

score 1 · Accepted Answer · answered Apr 05 '22 at 16:12

1

You can use NOT EXISTS in a MySql query, which (I hope) you can transform to Laravel code:

SELECT s1.user_id 
FROM statuses s1
WHERE s1.status = 'yes'
  AND NOT EXISTS (
            SELECT 1
            FROM statuses s2
            WHERE s2.user_id = s1.user_id AND s2.updateded_id > s1.updateded_id
          );

See the demo.

answered Apr 05 '22 at 16:12

forpas

160,666
10
38
76

That actually looks good... will try in a bit and update. – matisa Apr 06 '22 at 07:16

MySQL Select where in array of statuses and last occurrence equals specific value

3 Answers3