title: Java Spring JPA Reference and return only one result from join table
body: <p>I believe the code below is working correctly but my logic is flawed and so I'm looking for help getting the results I actually want. In addition I think my attempt on the JPA method is overly convoluted and there's likely a cleaner solution. I know I can likely use @Query but I want to try and get the method name solution working as well so I can learn where I went wrong.</p>
  <p>For simplicity I've stripped back the DB tables in this example, however I have Table1 which is the top level table and Table2 which sits below it and has a foreign key back to Table1.</p>
  <p>Table 1 -&gt; Table 2 is a one to many relationship, I am looking to write a JPA method that will pull back a record from Table 1 when given the Table1 ID and only the current effective record from Table2 (this will be where EffectiveDateTime column is most recent but NOT future dated). Using the example below I want it to only pull back ID 8.</p>
  <p>Unfortunately, I believe with the code I have below it recognises one of those records for Table2 is within the date range required and is therefore pulling all the records back that have a relation with the ID from Table 1. I could be wrong on this though and the logic could be flawed in a different way.</p>
  <p>Any help would be appreciated.</p>
  <p><strong>Table1 Entity Class</strong></p>
  <pre><code>@Entity
  @Table(name = &quot;TABLE1&quot;)
  public class Table1 {
      
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Column(name = &quot;ID&quot;, nullable = false, unique = true)
      private Integer id;
      
      @OneToMany(mappedBy = &quot;table1&quot;, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
      @Fetch(value = FetchMode.SUBSELECT)
      @JsonManagedReference
      private List&lt;Table2&gt; table2; 
  
      //Plus getters &amp; setters
  
  </code></pre>
  <p><strong>Table2 Entity Class</strong></p>
  <pre><code>@Entity
  @Table(name = &quot;TABLE2&quot;)
  public class Table2{
      
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      @Column(name = &quot;ID&quot;, nullable = false, unique = true)
      private Integer id;
      
      @ManyToOne(cascade = CascadeType.ALL)
      @JoinColumn(name = &quot;Table1Id&quot;)
      @JsonBackReference
      private Table1 table1;
      
      @Column(name = &quot;EffectiveDateTime&quot;, nullable = false)
      private LocalDateTime effectiveDateTime;
  
      //Plus getters &amp; setters
  </code></pre>
  <p><strong>Repo Class</strong></p>
  <pre><code>@Repository
  public interface Repository extends JpaRepository&lt;Table1, Integer&gt;, JpaSpecificationExecutor&lt;Table1&gt;  {
      
      public Optional&lt;Table1&gt; findTopByIdAndTable2EffectiveDateTimeLessThanEqualOrderByTable2EffectiveDateTimeDesc(int id, LocalDateTime now);
  }
  </code></pre>
  <p><strong>Current JSON return</strong></p>
  <pre><code>{
      &quot;id&quot;: 5
      &quot;Table2&quot;: [
          {
              &quot;id&quot;: 6,
              &quot;effectiveDateTime&quot;: &quot;2021-01-01T00:00:01&quot;
          },
          {
              &quot;id&quot;: 8,
              &quot;effectiveDateTime&quot;: &quot;2022-01-01T00:00:01&quot;
          },
          {
              &quot;id&quot;: 9,
              &quot;effectiveDateTime&quot;: &quot;2023-01-01T00:00:01&quot;
          }
      ]
  }
  </code></pre>