Filter across many to many relationship

Filter across many to many relationship

  

Hi,

I am needing to filter an aggregate (or an SQL) to provide a list from an entity that excludes records already selected and related to another entity.

I have the Participants entity and an Events entity that are related to each other via an EventParticipant entity that keeps track of the many to many relationship. 

In the Add Participants (to an event) screen there is a combo box with the list of names. Once a name is selected, it is added to a Table Record on the same screen. 

Once a name is added to the Table Record, I need that name to no longer be listed on the combo box.

I hope that makes sense.

I have tried multiple different ways of trying to filter via an aggregate and am going in circles.

Some help would be greatly appreciated.
Attached is a sample of what I am trying to do.

Thanks,
Glenn

Hi Glenn,

In Preparation GetparticipantName query you always get all the participant data that is wrong. always you get only those participant which is not associate event.


Thanks

Jitendra

Solution

Hi Glenn,

Without going through your eSpace (I'm on a tight schedule today :)), the way to select all X without an associated Y, is to LEFT JOIN ("With or Without") Y to X, then add a Filter that says "Y.Id = NullIdentifier()" (or NullTextIdentifier() for a Text identifier).

Solution

Hi Glenn Southward,


By using the below  Advanced SQL Query you can get the solution for the stated problem. Please find the attachment.

SyntaxEditor Code Snippet

SELECT {Participants}.[Id], {Participants}.[FirstName]+ ' ' + {Participants}.[LastName] from {Participants}
where {Participants}.[Id] not in 
(select {EventParticipant}.[ParticipantID]  from {EventParticipant} where {EventParticipant}.[EventID] = @EventID)


Check this out: https://sbsam.outsystemscloud.com/EventPlannerSolution/Events.aspx?(Not.Licensed.For.Production)=



Regards
Benjith Sam


Benjith Sam wrote:

Hi Glenn Southward,


By using the below  Advanced SQL Query you can get the solution for the stated problem. Please find the attachment.

SyntaxEditor Code Snippet

SELECT {Participants}.[Id], {Participants}.[FirstName]+ ' ' + {Participants}.[LastName] from {Participants}
where {Participants}.[Id] not in 
(select {EventParticipant}.[ParticipantID]  from {EventParticipant} where {EventParticipant}.[EventID] = @EventID)


Check this out: https://sbsam.outsystemscloud.com/EventPlannerSolution/Events.aspx?(Not.Licensed.For.Production)=



Regards
Benjith Sam


Hello, 

There is no need to use an SQL. A simple aggregate using Kilian's approach will do the trick. Just left join the Participant and ParticipantEvent appending to the Join condition 'and ParticipantEvent.EventId = EventId', so that only for that specifically event the second entity will be joined, setting the filters like Kilian said (ParticipantEvent.Id = NullIdentifier()).

The return will be a list of participants not yet in the event.

All of this using a dimple aggregate.

Always try to use an aggregate, if possible and much simpler than an SQL

Cheers.


Hi Everybody,

Thank you so much for the help and advise. The SQL is beautiful, but I am not ready for that yet. I'll stick with aggregates for now. I was getting pretty close with my experiments, but just didn't know about "appending to a Join". Still have a lot to learn. 

I didn't understand what Kilian was trying to tell say until Eduardo "spelled" it out for me. 

All your assistance is greatly appreciated.

Thanks,

Glenn

@Glenn: SQL is just for cases you can't use Aggregates, if you can avoid it, do so :).