使用查询DSL在数据库中查询近距离点

Question

我的申请中有以下实体：

• 会员
• 家庭广告
• 地址

在成员实体中：

@OneToOne(cascade=CascadeType.ALL)
    private Address address;
...
@OneToMany(fetch = FetchType.LAZY, mappedBy = "member")
private List<Advertisement> advertisements;

在< code >广告实体中:

@NotNull
@ManyToOne(fetch = FetchType.LAZY)
private Member member;

完整地址实体：

@Entity
public class Address {

    private String formattedAddress;
    private double latitude;
    private double longitude;
}

我想找到所有的家庭广告实例，其成员的地址在所需地址的20KM以内。

以下是我得出的结论：

QFamilyAdvertisement qFamilyAdvertisement = QFamilyAdvertisement.familyAdvertisement;

NumberPath<Double> lat = qFamilyAdvertisement.member.address.latitude;//NPE
NumberPath<Double> lng = qFamilyAdvertisement.member.address.longitude;
NumberPath<Double> distance = null;
NumberExpression<Double> formula = 
        (acos(cos(radians(Expressions.constant(requiredAddress.getLatitude())))
        .multiply(cos(radians(lat))
        .multiply(cos(radians(lng).subtract(radians(Expressions.constant(requiredAddress.getLongitude())))
        .add(sin(radians(Expressions.constant(requiredAddress.getLatitude())))
        .multiply(sin(radians(lat))))))))
        .multiply(Expressions.constant(6371)));

List<FamilyAdvertisement> foundFamilyAdvertisements = from(qFamilyAdvertisement.member.address).where(formula.as(distance).lt(20)).list(qFamilyAdvertisement);

但是，似乎我错误地使用了数字路径类，因为我不断得到一个NPE。任何人都可以帮我正确查询吗？

编辑：我已经改变了我的家庭广告实体如下：

@NotNull
@ManyToOne(fetch = FetchType.LAZY)
@QueryInit("address")
private Member member;

我现在得到以下异常：

java.lang.IllegalArgumentException: Only root paths are allowed for joins : familyAdvertisement.member.address
    com.mysema.query.DefaultQueryMetadata.ensureRoot(DefaultQueryMetadata.java:208)
    com.mysema.query.DefaultQueryMetadata.validateJoin(DefaultQueryMetadata.java:132)
    com.mysema.query.DefaultQueryMetadata.addJoin(DefaultQueryMetadata.java:118)
    com.mysema.query.DefaultQueryMetadata.addJoin(DefaultQueryMetadata.java:110)
    com.mysema.query.support.QueryMixin.from(QueryMixin.java:161)
    com.mysema.query.jpa.JPQLQueryBase.from(JPQLQueryBase.java:96)
    com.mysema.query.jpa.impl.JPAQuery.from(JPAQuery.java:30)
    org.springframework.data.jpa.repository.support.Querydsl.createQuery(Querydsl.java:88)
    org.springframework.data.jpa.repository.support.QueryDslRepositorySupport.from(QueryDslRepositorySupport.java:94)
    com.bignibou.repository.FamilyAdvertisementRepositoryImpl.performFamilyAdvertisementSearch(FamilyAdvertisementRepositoryImpl.java:64)

第64行是这样的：

List<FamilyAdvertisement> foundFamilyAdvertisements = from(qFamilyAdvertisement.member.address).where(formula.as(distance).lt(20)).list(qFamilyAdvertisement);

任何线索现在出了什么问题？

edit2:我忘了提到< code>FamilyAdvertisement扩展了< code>Advertisement，并且< code>member变量在< code>Advertisement中。

edit3:下面是我试图用QueryDSL重现的SQL：

select * from family_advertisement a inner join member m
on a.member = m.id
where m.address
in (
SELECT id 
FROM address where 
 6371 * 
acos( cos( radians(48.8558966) ) 
* cos( radians( latitude ) ) 
* cos( radians( longitude ) - radians(2.3622728) ) 
+ sin( radians(48.8558966) )
* sin( radians( latitude ) )
) < 20);

我试过这样的东西:

List<FamilyAdvertisement> foundFamilyAdvertisements = from(qFamilyAdvertisement).where(qFamilyAdvertisement.member.address.in(

                new JPASubQuery().from(QAddress.address).where(formula.lt(20)))

                ).list(qFamilyAdvertisement);

上面给出了公式，但我不确定如何在QueryDSL中表达不相关的子查询，尤其是上面的in运算符似乎有问题…

编辑4：

以下子查询现在有效：

List<FamilyAdvertisement> foundFamilyAdvertisements = 
        from(qFamilyAdvertisement).where(qFamilyAdvertisement.member.address.in(new JPASubQuery().from(QAddress.address).where(formula.lt(20)).list(QAddress.address))).list(qFamilyAdvertisement);

Answer 1

这个路径对于急切的初始化来说太长了

qFamilyAdvertisement.member.address.latitude;

请在此处阅读有关查询 http://www.querydsl.com/static/querydsl/3.1.0/reference/html/ch03s04.html#d0e1699 中路径初始化的更多信息