Where with Subquery

Laravel Example

$relations = DB::table('tenten_pms_product as a') 
	->join('tenten_pms_product_category as b', 'b.product_id', '=', 'a.id') 
	->select('b.product_id') 
	->where('b.category_id', $cat_id) 
	->whereRaw(
		'(select count(*) from tenten_pms_product_model as suba where suba.product_id = a.id and suba.enabled = 1) > 0') 
	->orderBy('a.state_new', 'desc') 
	->orderBy('b.sort_order', 'asc') 
	->orderBy('a.created_at', 'desc') 
	->get(); 
//logg($relations);

SQL

SELECT a.*, 
(select count(*) from tenten_pms_product_model as suba 
where suba.product_id = a.id
and suba.enabled = 1) as model_count
from tenten_pms_product as a
join `tenten_pms_product_category` as b on b.product_id = a.id
WHERE b.`category_id` = '41' 
and (select count(*) from tenten_pms_product_model as suba 
where suba.product_id = a.id
and suba.enabled = 1) > 0
ORDER BY a.state_new desc, b.`sort_order` asc
LIMIT 50

Reference:

  1. https://justcode.ikeepstudying.com/2016/08/mysql%E5%85%A5%E9%97%A8-%E4%B9%9D-%E5%AD%90%E6%9F%A5%E8%AF%A2-subquery/
  2. https://ithelp.ithome.com.tw/articles/10032121