Get the details but ignore user if exist another higher level

user_13426818 0
Viewed 57

I have a table looks like

id  user_id  level

1     1        1
2     1        2
3     2        1
4     3        1

Here user id 1 exist in level 1 and level 2 Now when we count group by level then in counting we want ignore user_id 1 from level 1 cause it exist another group. we want to consider only one group existing and higher group.

I have done only group count but cant understand how ignore counting.

My current query is

UserCertificate::with('user')
    ->where('level','1')
    ->distinct('level')
    ->orderBy('id','ASC')
    ->get();

My Query return counting

id  user_id  level

1     1        1
3     2        1
4     3        1

But I want looks like

id  user_id  level

3     2        1
4     3        1

User Id 1 will be ignored cause it exist in level 2

laravel-5 laravel mysql php
2 Answers

Try using sub query

select * from user
where user_id != (
select user_id from user where  level != 1
) and level = 1
group by user_id,level

in your code like this

UserCertificate::with('user')->where('level','1')->where('user_id != (select user_id from user where level != 1)')->group_by(array("user_id", "level"))->orderBy('id','ASC')->get();

Demo: http://sqlfiddle.com/#!9/cd214e/30

user_25959400

This can be solved by join table itself by user_id like:

select UserCertificate.* 
from UserCertificate
left join UserCertificate UserCertificate2 on 
    UserCertificate.user_id = UserCertificate2.user_id and 
    UserCertificate.level < UserCertificate2.level
where UserCertificate.level = 1 and UserCertificate2.level is null
;

Here it can be tested

result:

id    user_id    level
3     2          1
4     3          1
user_33993560
Related