1

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)?

Karma J
  • 59
  • 4

0 Answers0