Join non-unique column in spring jpa

Question

I was hopping to find an answer to my probleme on this here forum. My problem is as follows, I have two classes :

@Entity
@Table(name = "a")
public class A implements Serializable{

  @Id
  private String id = UUID.randomUUID().toString();

  @Column(name = "REFERENCE_ID")
  private String referenceId;

  @Column(name = "VERSION")
  private String version;
}

And

@Entity
@Table(name = "b")
public class B{

  @Id
  private String id = UUID.randomUUID().toString();

  @Column(name = "REFERENCE")
  private String reference;

  @ManyToMany(fetch = FetchType.LAZY)
  @NotFound(action = NotFoundAction.IGNORE)
  @JoinColumnsOrFormulas({
        @JoinColumnOrFormula(formula = @JoinFormula(value = 
                "(select r from A r where r.reference_id = reference_id order by r.version desc limit 1)",
                referencedColumnName = "reference_id")),
        @JoinColumnOrFormula(column = @JoinColumn(name = "reference_id",
        referencedColumnName = "reference_id", insertable = false))
        })
  private A referenceId;
}

The thing is reference_id is not a unique key in the b table and was just an indicative value in table A so in order to fetch the entire correspondent row I had to do some filtering with the formula in my join annotation. When I try to fetch my data I get the following error

[Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause org.postgresql.util.PSQLException: ERROR: relation "a" does not exist Position : 309

EDIT

ACtually t works as intended when changing my join formula to

 @JoinFormula(value = 
            "(select r from schema_A r where r.reference_id = reference_id order by r.version desc limit 1)",
            referencedColumnName = "reference_id"))

the problem now is that the code is intended to work on multipple envirnments as for my application.yml it looks a bit like this;

jpa:
database: POSTGRESQL
show-sql: true
hibernate:
  ddl-auto: update
properties:
  hibernate:
    format_sql: true
    default_schema: schema
    jdbc:
      lob:
        non_contextual_creation: true
      time_zone: UTC

Thanks for your responses :)

Answer 1

I agree with Simon's comment. For Postgres (and relational databases in general), the word "table" and "relation" are the same and where the term "relational" comes from. So, when it says "Can't find relation B" it literally means "Can't find a table called B".

You should check your connection settings for the schema to see if those tables have/haven't been defined. If it's not obvious, maybe add/edit the question accordingly with your connection settings & appropriate debugging showing you DO see the relations (tables) there.