SQL JOIN 5 個 TABLE 該如何寫得好一些??

如果用laravel 的ORM 是不是可以寫得簡化及好維護一點??

不用ORM該怎麼寫??

試過把lastreply* 那3行寫到left join (subquery), 但subquery不能用order by, 拿不到正確資料

select shares.*,
users.username, users.email,
categories.name as cat_name,
(select count(*) from share_like where share_like.share_id = shares.id) as likes,
(select count(*) from comments where comments.share_id = shares.id) as comments_count,
(select comments.created_at
   from comments
   where comments.share_id = shares.id
   order by comments.created_at desc
   limit 1) as last_reply,
(select users.email
   from users
   left join comments
   on comments.user_id = users.id
   where comments.share_id = shares.id
   order by comments.created_at desc
   limit 1) as last_reply_email,
(select users.username
   from users
   left join comments
   on comments.user_id = users.id
   where comments.share_id = shares.id
   order by comments.created_at desc
   limit 1) as last_reply_username
from shares
inner join users
on users.id = shares.user_id
inner join categories
on categories.id = shares.cat_id
order by shares.created_at desc