Is it possible to join @joinTable using JPASQLQuery class?

[eari100]

For example, when the source code is as follows

@NoArgsConstructor
@Entity
public class Summoner implements Serializable {
    @Id@Column(length=56)
    private String id;
    @Column(name="account_id", length=56)
    private String accountId;
    private int profileIconId;
    private long revisionDate;
    private String name;
    @Column(length=78)
    private String puuid;
    private long summonerLevel;

    @OneToMany
    @JoinTable(name="summoner_match",
            joinColumns = @JoinColumn(name="account_id"),
            inverseJoinColumns = @JoinColumn(name="game_id")
    )
    private List<Match> matches = new ArrayList<>();

    //...
}

@NoArgsConstructor
@Entity
public class Match {
    @Id
    @Column(name="game_id")
    private long gameId;
    private int queueId;
    private String platformId;
    private int seasonId;
    private long gameCreation;
    private long gameDuration;

    @OneToMany
    @JoinColumn(name = "game_id", referencedColumnName = "game_id")
    List<Participant> participants = new ArrayList<>();

    //...
}

I want to generate the following sql using JPASQLQuery class

-- h2 database
SELECT
    *, row_number()OVER()
FROM 
    summoner s
LEFT OUTER JOIN
    summoner_match sm
on
   s.account_id= sm.account_id
LEFT OUTER JOIN
    match m
on
   sm.game_id = m.game_id 

I tried and failed like below.

package gg.om.omgg.domain.summoner;
import com.querydsl.core.Tuple;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.querydsl.jpa.sql.JPASQLQuery;
import com.querydsl.sql.SQLExpressions;
import gg.om.omgg.domain.match.QMatch;
import gg.om.omgg.domain.participant.QParticipant;
import gg.om.omgg.web.dto.SummonerIntegrationInformationResponseDTO;
import lombok.RequiredArgsConstructor;

@RequiredArgsConstructor
public class SummonerCustomRepositoryImpl implements SummonerCustomRepository {
    // JPASQLQuery class is registered as @Bean in advance
    private final JPASQLQuery jpasqlQuery;

    @Override
    public void mySolution() {
        QSummoner summoner = QSummoner.summoner;
        QMatch match = QMatch.match;

        // How do I join summoner and summoner_match?
        jpasqlQuery
                .select(summoner, match, SQLExpressions.rowNumber().over())
                .from(summoner)                                                                                              
                .leftJoin(summoner_match).on(summoner.accountId.eq(summoner_match.accountId))
                .leftJoin(match)
                .on(summoner_match.gameId.eq(match.gameId))
                .fetch();
   }
}

(Please tell me if there is any other method provided by querydsl other than using the JPASQLQuery class.)

[jwgmeligmeyling]

Just use a JPQL query and create a custom function for the "ROW_NUMBER" function. You can also use blaze-persistence-integration-querydsl-expressions to add window function support to JPA: https://persistence.blazebit.com/documentation/1.5/core/manual/en_US/#querydsl-integration

JPASQLFunctions only serve a very specific use case and I doubt that associtation joins are supported.

[eari100]

@jwgmeligmeyling
Thanks to you, I can sleep

[eari100]

Seems to have found a way.
Please check if there is a problem.

    @Override
    public void mySolution() {
        QSummoner summoner = QSummoner.summoner;
        QMatch match = QMatch.match;
        // Create Qclass for @JoinTable
        QSummoner summoner_match1 = new QSummoner("summoner_match");
        QMatch summoner_match2 = new QMatch("summoner_match");

    
        jpasqlQuery
                .select(summoner, match, SQLExpressions.rowNumber().over())
                .from(summoner)                                                                                              
                .leftJoin(summoner_match1).on(summoner.accountId.eq(summoner_match1.accountId))
                .leftJoin(match).on(summoner_match2.gameId.eq(match.gameId))
                .fetch();
   }