I have a unidirectional one-to-one relation. what I want to achieve is create a join on clause using CASE and sending variables to it so that I can change the join column (any other suggestions to change join column are also welcomed)
My query that works is like :
Code:
SELECT d.Description, d.AccountId, d.PartnerId, a.FormattedName FROM
InvoiceDesigns d
INNER JOIN UserAccount a
ON
a.id =
CASE
WHEN d.PartnerId != -1 AND d.AccountId = 1 THEN d.PartnerId
WHEN d.PartnerId = 1 THEN d.AccountId
ELSE 1
END
WHERE
d.AccountId = 1 OR PartnerId = 1
Normal join is working fine when I want to get the AccountData but I need to change the column to get the related FormattedName, it is either name of the partner or the account owner. I tried @Where, @Filter, @JoinColumnOrFormula but I wasn't able to achieve this query.
My classes with @Where basically look like this:
Design class:
Code:
@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="id")
int id;
@Column(name="AccountId")
int accountId;
@Column(name="PartnerId")
int partnerId;
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "AccountId", referencedColumnName = "id", insertable = false, updatable = false, nullable = true)
@Where(clause = "CASE " +
"WHEN PartnerId != -1 AND AccountId = :accountId THEN PartnerId " +
"WHEN PartnerId = :accountId THEN AccountId " +
"ELSE :accountId " +
"END")
AccountData account;
Account class:
Code:
@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="id")
int id;
@Column(name="FormattedName")
String formattedName;
Here you can see that I want to send an ID to query as parameter using ":accountId" which will be used to define the join column.
Thanks in advance
Login
FAQ
Search