쿼리 리팩토링하기 1

aws 를 수정해야하는 상황이 닥침

쿼리가 너무 불필요하게 작성되어있어서 쿼리를 리팩토링했다

조건절이 많이 들어간게 미적으로 별론데 현재 내가 할수있는선까지는 수정했다 :)

---

Before

select 
            c.id
            , c.come_leave
            , p.is_come
            , p.path
            , e.id as employee_id
            , e.image
            , d.company_id
            , o.account
            from (
            select id, come as come_leave, employee_id, true as is_come from commute_record where come is not null and come_match is null
            union
            select id, `leave` as come_leave, employee_id, false as is_come from commute_record where `leave` is not null and leave_match is null) c 
            inner join photo p on c.id = p.commute_id and c.is_come = p.is_come
            inner join employee e on c.employee_id = e.id
            inner join division d on e.division_id = d.id
            inner join company o on d.company_id = o.id
            where e.image is not null 
            and d.company_id = 8
            order by c.id, p.is_come desc 
            limit 10;

AFTER

SELECT
 cr.id,
 p.path as commute_pic,
 e.image as employee_pic
FROM
 employee as e
INNER JOIN commute_record as cr on e.id = cr.employee_id
INNER JOIN photo as p on cr.id = p.commute_id
WHERE p.is_come = true AND e.image is not null AND cr.come is not null AND cr.come_match is null AND cr.division_id = 18;