英文:
INNER JOIN collections in Spring Data MongoDB
问题
@Document(collection = "people")
public class Person {
@Id
private String id;
private String name;
private String petId;
// Getters, setters, constructors and etc.
}
@Document(collection = "pets")
public class Pet {
@Id
private String id;
private String name;
private PetType petType; // Dog, Cat etc.
// Getters, setters, constructors and etc.
}
// ...
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import java.util.List;
public interface PetRepository extends MongoRepository<Pet, String>, PetCustomRepository {
}
public interface PetCustomRepository {
List<Pet> findAllByPetTypeAndPersonName(PetType type, String personName, Pageable pageable);
}
public class PetCustomRepositoryImpl implements PetCustomRepository {
@Autowired
private MongoTemplate mongoTemplate;
@Override
public List<Pet> findAllByPetTypeAndPersonName(PetType petType, String personName, Pageable pageable) {
LookupOperation lookup = LookupOperation.newLookup()
.from("people")
.localField("_id")
.foreignField("petId")
.as("join_people");
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.match(Criteria.where("petType").is(petType)),
lookup,
Aggregation.match(Criteria.where("join_people.name").is(personName)),
Aggregation.skip(pageable.getPageNumber() * pageable.getPageSize()),
Aggregation.limit(pageable.getPageSize()));
return mongoTemplate.aggregate(aggregation, Pet.class, Pet.class).getMappedResults();
}
}
Please note that the provided code appears to be related to a Spring Boot application using Spring Data MongoDB for database interactions. The code is attempting to perform an INNER JOIN-like operation between the "pets" and "people" collections based on the provided sample data models. The findAllByPetTypeAndPersonName() method in the custom repository implementation is intended to retrieve pets of a certain type that belong to a specific person by name. If this method is returning an empty list, there might be several reasons to investigate, including data inconsistencies, misconfigured collection names, or issues with the data retrieval logic.
英文:
How do I implement INNER JOIN in Spring Mongo?
Stupid sample just for example, it's actually incorrect, I just want to show many-to-many relation:
@Document(collection = "people")
public class Person {
@Id
private String id;
private String name;
private String petId;
// Getters, setters, constructors and etc.
}
@Document(collection = "pets")
public class Pet {
@Id
private String id;
private String name;
private PetType petType; // Dog, Cat etc.
// Getters, setters, constructors and etc.
}
If I want to find all dogs that belong to John Smith, how should I do it? I need a query like this:
SELECT
pt.*
FROM
pets AS pt INNER JOIN people AS pe ON (pt.id = pe.petId)
WHERE
pt.petType = ${input_petType}
AND pe.name = ${input_name}
It means that I have two conditions in collection Pet and collection Person:
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import java.util.List;
public interface PetRepository extends MongoRepository<Pet, String>, PetCustomRepository {
}
public interface PetCustomRepository {
List<Pet> findAllByPetTypeAndPersonName(PetType type, String personName, Pageable pageable);
}
public class PetCustomRepositoryImpl implements PetCustomRepository {
@Autowired
private MongoTemplate mongoTemplate;
@Override
public List<Pet> findAllByPetTypeAndPersonName(PetType petType, String personName, Pageable pageable) {
LookupOperation lookup = LookupOperation.newLookup()
.from("people")
.localField("_id")
.foreignField("petId")
.as("join_people");
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.match(Criteria.where("petType").is(petType)),
lookup,
Aggregation.match(Criteria.where("join_people.name").is(personName)),
Aggregation.skip(pageable.getPageNumber() * pageable.getPageSize()),
Aggregation.limit(pageable.getPageSize()));
return mongoTemplate.aggregate(aggregation, Pet.class, Pet.class).getMappedResults();
}
}
The findAllByPetTypeAndPersonName() method returns empty list. What am I doing wrong?
答案1
得分: 0
你的Pageable存在问题。尝试移除它，应该会返回结果。
英文:
There is a problem with your Pageable. Try removing it, it should return results
答案2
得分: 0
尝试过吗？
LookupOperation lookup = LookupOperation.newLookup()
.from("people")
.localField("id")
.foreignField("petId")
.as("join_people");
英文:
Have you tried?
LookupOperation lookup = LookupOperation.newLookup()
.from("people")
.localField("id")
.foreignField("petId")
.as("join_people");
专注分享java语言的经验与见解，让所有开发者获益！
评论