0

How would I transpile this query to Laravel style

SELECT 
    gr.name,
    p.ma_status AS status,
    COUNT(p.ma_status) AS total
FROM accounts u
LEFT JOIN accounts_prop p ON p.account_id = u.account_id 
AND ( 
       SELECT j.iid
       FROM accounts_prop AS j  
       WHERE u.account_id = j.account_id 
       AND j.ma_status IS NOT NULL
       ORDER BY j.von DESC  LIMIT 1 
) = p.iid
LEFT JOIN `deprecators` gr ON gr.id = p.group_id 
LEFT JOIN `deprecators` unit ON unit.id = p.unit_id 
LEFT JOIN `deprecators` team ON team.id = p.team_id 
WHERE p.group_id IS NOT NULL
AND u.account_status = 'A'
GROUP BY p.ma_status, gr.id

I do not have any idea how would I go with the following statement 

LEFT JOIN accounts_prop p ON p.account_id = u.account_id 
AND ( 
       SELECT j.iid
       FROM accounts_prop AS j  
       WHERE u.account_id = j.account_id 
       AND j.ma_status IS NOT NULL
       ORDER BY j.von DESC  LIMIT 1 
) = p.iid
fefe
  • 8,755
  • 27
  • 104
  • 180
  • 1
    [See here](https://stackoverflow.com/questions/18079281/laravel-fluent-query-builder-join-with-subquery) for how to handle the subquery join. I won't attempt an answer, because you are asking for a lot of Laravel code. – Tim Biegeleisen Sep 18 '18 at 07:57
  • thanks I take a look on the examples – fefe Sep 18 '18 at 08:01

0 Answers0