CODEWITHSUNDEEP
sqlhibernateentityhqlrelationship
sokheng
sokheng

Reputation: 31

HQL - how to query data from one-to-many relationship and condition on second entity field

This is what i expected in SQL query string:

SELECT     dbo.FRIEND.FriendId, dbo.FRIEND.MemberId, dbo.FRIEND.FriendMemberId, dbo.FRIEND.DateAdded
FROM         dbo.FRIEND INNER JOIN
                      dbo.MEMBER ON dbo.FRIEND.FriendMemberId = dbo.MEMBER.MemberId
WHERE     (dbo.MEMBER.Activate = 1)

This is my entities with relationship : Entity Friend field

@Id
@Column(name = "[FriendId]")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long              friendId;

@Column(name = "[MemberId]")
private Long              memberId;

@OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinColumn(name = "[FriendMemberId]")
private Member            friendMember;

@Column(name = "[DateAdded]")
private Date              dateAdded;

Entity Member field

@Id
@Column(name = "[MemberId]")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long              memberId;

@Column(name = "[Activate]", columnDefinition = "boolean default true")
private boolean           activate;

Here is my HQL query :

FROM Friend as f Left join f.Member as m WHERE f.MemberId = :memberId AND m.activate = true

but i got error. so how should i write HQL query get data and its condition depend on member.activate ?

Upvotes: 1

Views: 3307

Answers (2)

tmarthal
tmarthal

Reputation: 1528

You can access subtables using the dot notation and Hibernate will take care of the join for you.

SELECT FROM Friend f WHERE f.friendMember.activate = true

That's part of why HQL is so nice.

Upvotes: 2

Sanghyun Lee
Sanghyun Lee

Reputation: 23002

Your query has some typo. You should use field name instead of type when you access to field.

Try this:

FROM Friend as f Left join f.friendMember as m WHERE f.memberId = :memberId AND m.activate = true

If you have other problem. Please show me the log.

Upvotes: 1

Related Questions