How to take into account the parameters of the child component when sorting data?

Question

MySQL

Good evening. The task is as follows. There is a list of employees. Some workers have an assistant. The assistant is distinguished by the presence of the trainer_id parameter - the ID of the worker whose assistant this worker is.
Workers have a column that is responsible for whether an employee is at work today "is_work".
We need to display the list of workers, excluding the list of assistants from it (trainer_id = null). At the same time, sort them according to whether they are at work. Considering that his assistant may be at work.
That is, if there is no employee, but there is his assistant, it is considered that the employee is at work.
You can answer both yf Mysql and yf RubyOnRails. Thank you.

Answer 1

Do a LEFT JOIN table on itself and select / sort as you need.
Like this
SELECT w.* , IF(IFNULL(h.is_work, 0) = 0, w.is_work, h.is_work) as any_is_work
FROM table as w
LEFT JOIN table as h
ON w.id = h.trainer_id
where w. trainer_id is not null
ORDER BY any_is_work