How to make nested queries in Laravel in a database selection?

Question

SQL

Greetings!
Wrote a request that fits the purpose.
How to translate it under Laravel for the builder?
Those. how to make a nested select with options.
Use selectRaw?
Thank you.

select (
           select count(cp.product_id)
           from product as cp
           where cp.color_id = :cid
             and cp.pr_id = :pid
             and cp.manuf_id = :mid
             and cp.comp = p.comp
       ) as total,
       p.comp,
       IF(p.comp = 1, 'Совместимый', 'Оригинальный') as name
from product p
group by p.comp;

Answer 1

->withCount(),->addSelect()