CODEWITHSUNDEEP
javaspringhibernatejpaquerydsl
Eric J Turley
Eric J Turley

Reputation: 350

QueryDsl - JPA query on multiple fields of associated entity

A few simple entities:

enter image description here

Tech stack

I want to make a query with this effect:

Select associated Meetups where BOTH of the following are true for MeetupCampaigns:
* MeetupCampaign.id = x
* MeetupCampaign.approvalStatus = y

Importantly, BOTH must be true

I'm able to get results where either of the criteria are true, but not able to filter for both

I'm passing a predicate to the repository.findAll()

I'm not sure whether to use the MeetupRepository or the MeetupCampaignRepository

This predicate works, when fetching just the MeetupCampaigns:

meetupCampaign.approvalStatus.eq(approvalStatus).and(meetupCampaign.campaign.id.eq(campaignId));

But fails in the indirection when I try to get the Meetups:

meetup.meetupCampaigns.any().campaign.id.eq(campaignId).and(meetup.meetupCampaigns.any().approvalStatus.eq(approvalStatus));

In this case, I get any MeetupCampaign which has either condition true.

Consider this dataset:

<!-- Should be selected -->
    <meetup_campaign id="600" meetup_id="500" campaign_id="200" approval_status="ACCEPTED"/>
    <meetup_campaign id="605" meetup_id="504" campaign_id="200" approval_status="ACCEPTED"/>
<!-- Rejected because meetupCampaign status declined -->
    <meetup_campaign id="601" meetup_id="501" campaign_id="200" approval_status="DECLINED"/>
<!-- Rejected because wrong campaign id -->
    <meetup_campaign id="602" meetup_id="500" campaign_id="201" approval_status="ACCEPTED"/>
    <meetup_campaign id="606" meetup_id="501" campaign_id="201" approval_status="ACCEPTED"/>
    <meetup_campaign id="603" meetup_id="502" campaign_id="201" approval_status="ACCEPTED"/>
<!-- Rejected on both counts -->
    <meetup_campaign id="604" meetup_id="503" campaign_id="201" approval_status="DECLINED"/>

Note that meetups 500 and 504 should be selected. 501 is the important one here. It should NOT be selected, because the two MeetupCampaigns associated with it each fail one of the criteria.

Can you help me write this query?

Updates

(Simplified) Code for the entities:

@Entity
public class Meetup {

    @Id
    @Column(nullable = false, updatable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    protected Long id;

    @Fetch(FetchMode.SELECT)
    @RestResource(exported = false)
    @OneToMany(mappedBy = "meetup", fetch = FetchType.EAGER, orphanRemoval = true)
    private Set<MeetupCampaign> meetupCampaigns = new HashSet<>();

}

@Entity
public class MeetupCampaign {

    @Id
    @Column(nullable = false, updatable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    protected Long id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(nullable = false, updatable = false)
    private Campaign campaign;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(nullable = false, updatable = false)
    private Meetup meetup;

    @Enumerated(EnumType.STRING)
    private ApprovalStatus approvalStatus;

}

@Entity
public class Campaign {

    @Id
    @Column(nullable = false, updatable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    protected Long id;

}

Upvotes: 2

Views: 7240

Answers (1)

Nicholas Soltau
Nicholas Soltau

Reputation: 36

Found an answer here: QueryDsl - subquery in collection expression

You can use a subquery expression to find all the meetupCampaigns with those two properties and then use the SimpleExpression.in() to find meetups related to those meetupCampaigns.

ex.

meetup.in(JPAExpressions.select(meetupCampaign.meetup).from(meetupCampaign).where(meetupCampaign.campaign.id.eq(campaignId).and(meetupCampaign.approvalStatus.eq(status))).fetchAll());

Upvotes: 2

Related Questions