I'm working with mySQL and I have the API using spring data. Entities and Repositories are configured and working. I have a football matches table called "Matches" and each match has a homeTeam and an awayTeam.
I need to retrieve a list of all matches where "Team 1" and "Team 2" have played so I can build their "match history"
Here is my repository:
public interface MatchRepository extends JpaRepository<Match,Long> {
List<Match> findByHomeTeamOrAwayTeam(
@Param("homeTeam") Team homeTeam,
@Param("awayTeam") Team awayTeam
);
}
This works fine, it returns every match where "Team 1" was the homeTeam and "Team 2" was the awayTeam. I can also do findByHomeTeamAndAwayTeam and it returns the same list but filtered by the matches they both played.
Now the problem I can't get around is how can I use the "IN" keyword to fetch all matches that had "Team 1" and "Team 2" despite home or away situation.
For now I'm doing 2 calls to the same endpoint inverting the parameters but is there a way to represent the following???:
Select * from matches where homeTeam IN (1, 2) AND awayTeam IN (1, 2);