Getting max in a join table

#1

I have a table users and degrees which is joined with users_degrees.
a degree has a level. I need the highest degree per user. How do i do this best/easiest with CakePHP3 ?

SELECT user_id,  level, degree_name from users_degrees ud 
LEFT JOIN degrees d on ud.degree_id = d.id 
group by applicant_id, degree_id, level, en;

This query will get me the results, still not only the max.

user_id level      degree_name
1       1          degree_1
1       3          degree_3
1       4          degree_4
2       3          degree_3
2       4          degree_4
3       1          degree_1
3       4          degree_4

i think i need to have max(level) somewhere, but can’t figure out where, and how it would translate to Cake.

#2

here’s a query that seems to get the results i need, but is this most efficient?
and how do i transform it to cake3?

SELECT d1.level,d1.en from applicants_degrees ad1
left join degrees d1 ON d1.id = ad1.degree_id
WHERE level = (
    SELECT MAX(level) from degrees d2
    left join applicants_degrees ad2 ON d2.id = ad2.degree_id
    where ad1.applicant_id = ad2.applicant_id
);
#3

you are not using BelongsToMany Associations Properly, according to this your middle table should be degrees_users

Then if you made all set then you can aceess Required Result by using this queery

// In a controller or table method.
$query = $users->('first', array('conditions' => array('user_id' => $this->uid), 'order'=>'level     DESC'))->contain(['Degrees']);

Hope This will solve you issue :blush: :yum: