MySQL joining two tables and counting based on conditions
I just found the answer and wanted to share it:

SELECT 
  dir_cats.*, 
  SUM(CASE WHEN dir_sites.site_lang = "ar" THEN 1 ELSE 0 END) AS ar_count, 
  SUM(CASE WHEN dir_sites.site_lang = "en" THEN 1 ELSE 0 END) AS en_count,
  SUM(CASE WHEN dir_sites.site_lang = "fr" THEN 1 ELSE 0 END) AS fr_count 
FROM (dir_cats) 
  LEFT JOIN dir_sites 
    ON site_cat_id = cat_id 
GROUP BY dir_sites.site_cat_id