title: JPA Custom Query with multiple Inner Joins
body: <p>I am new to spring boot and need help on custom JPA Query with multiple Inner Joins. Basically I need to convert the following SQL query into JPA Query: </p>
  
  <pre><code>SELECT count(uc.certifications_groups)
      FROM firms f
      INNER JOIN firms_users_map fum on fum.firm_realm_id = f.firm_realm_id
      INNER JOIN users u on u.global_auth_id = fum.global_auth_id
      INNER JOIN users_certifications uc on uc.global_auth_id = u.global_auth_id
      WHERE f.firm_realm_id = 1 and uc.certifications_groups = 'qbo'
      GROUP BY uc.certifications_groups;
  </code></pre>
  
  <p>Below is the code structure: </p>
  
  <p><strong>FirmEntity</strong></p>
  
  <pre><code>@Entity(name = "firms")
  @Table(name = "firms")
  public class FirmEntity {
  
      @Id
      private long firmRealmId;
  
      @JoinColumn(name = "tier_name", nullable = false)
      private String tierName;
  
      @Column(nullable = false)
      private int currentPoints;
  
      @Column(nullable = true)
      private Date gracePeriodEnd;
  
      @ManyToMany
      @JoinTable(
          name = "firms_users_map",
          inverseJoinColumns = { @JoinColumn(name = "global_auth_id") },
          joinColumns = { @JoinColumn(name = "firm_realm_id") }
      )
      private Set&lt;UserEntity&gt; users;
  
      public Set&lt;UserEntity&gt; getUsers() {
          return users;
      }
  
      public void setUsers(Set&lt;UserEntity&gt; users) {
          this.users = users;
      }
  
      @Entity
      @Table(name = "users")
      public class UserEntity {
  
      @Id
      @Column(name="global_auth_id", nullable = false)
      private long global_auth_id;
  
  
      public long getGlobal_auth_id() {
          return global_auth_id;
      }
  
      public void setGlobal_auth_id(long global_auth_id) {
          this.global_auth_id = global_auth_id;
      }
  
      @OneToMany
      @JoinTable(
          name = "user_certifications",
          inverseJoinColumns = { @JoinColumn(name = "certification_group") },
          joinColumns = {@JoinColumn(name="global_auth_id")}
      )
      private Set&lt;users_certificationsEntity&gt; users_certificationsEntity;
  
  
      public Set&lt;users_certificationsEntity&gt; getUser_certifications() {
          return users_certificationsEntity;
      }
  
      public void setUser_certifications(Set&lt;users_certificationsEntity&gt; user_certifications) {
          this.users_certificationsEntity = user_certifications;
      }
  </code></pre>
  
  <p><strong>FirmRepository</strong></p>
  
  <pre><code>public interface FirmRepository extends CrudRepository&lt;FirmEntity, Long&gt; {
  
  
   @Query("select count(c) from firms f inner join f.users c inner join 
   c.users_certificationsEntity x where f.firmRealmId = ?1 "
          + "and x.certifications_groups=qbo_adv")
  int count_qbo_adv_Certification(long firmRealmId)
  </code></pre>
  
  <p><strong>CertificationCountSignalImpl</strong></p>
  
  <pre><code>public class CertificationCountSignalImpl implements Signal {
  
      @Autowired
      private FirmRepository firmRepository;
  
      @Autowired
      private SignalMetadataRepository signalMetadataRepository;
  
      @Autowired
      private CertificationCountSignalImpl certificationCountSignalImpl;
  
  
      private int get_qbo_certificationPoints(long firmRealmId) {
          if(firmRealmId &lt;=0) {
              throw new IllegalArgumentException();
          }
          int clientCount = firmRepository.countAllClients(firmRealmId);
  
          int CertificationsCount_QBO_ByRealm_id = firmRepository.count_qbo_Certifications(firmRealmId);  
  
          int totalPoints =0; 
          int pointsCounter =0;
  </code></pre>