Below is the corresponding Mysql query and its a working query :
select tab4.id, tab4.name, tab7.city, tab7.state, tab7.phone, tab5.status_priority,
tab5.model,tab5.code,
tab5.number, tab5.ds_formatted_timestamp
group_concat( distinct tab6.position),
coalesce(b.counts, 0) as count
from table1 tab1
inner join table2 tab2 on tab1.organization_id=tab2.organization_id
inner join table3 tab3 on tab2.organization_id=tab3.organization_id
inner join table4 tab4 on tab3.site_id=tab4.site_id
inner join table5 tab5 on tab4.site_id=tab5.site_id
inner join(select count(component_name) as counts, device_id from table6 group by device_id) b on ?
b.device_id =tab5.device_id
left outer join table6 tab6 on tab5.id=tab6.id
left outer join table7 tab7 on tab4.address_id=tab7.address_id
where tab1.user_id='test@gmail.com'
group by tab5.id
order by tab5.status_priority desc;
Below Criteriabuilder is working code
Root<table1> user = criteriaQuery.from(table1.class);`
Join<Object, Object> user2 = user.join("table2", JoinType.INNER);
Join<Object, Object> user3 = user2.join("table3", JoinType.INNER);
Join<Object, Object> user4 = user3.join("table4", JoinType.INNER);
Join<Object, Object> user5 = user4.join("table5", JoinType.INNER);
Join<Object, Object> user6 = user5.join("table6", JoinType.LEFT);
Join<Object, Object> user7 = user5.join("table7", JoinType.LEFT);
I have added the following lines, its working but dont know how to write using criteriabuilder
coalesce(b.counts, 0) as count `
join(select count(component_name) as counts, device_id from table6 group by device_id) b on b.device_id =tab5.device_id
How to write the subquery for this using criteriabuilder (JPQL)?