Hello everyone, I’m working on a professional social network application using couchbase server 4.1 with spring data couchbase 2.2.4 in a spring boot application using java 1.8.
I want to replace the next stream which searches a LikeEntity with specific company in the database by a N1QL based query which searches a user with the same previous likeEntity unnested: Here is the service containing the stream to replace by the query:
@Service
class CompanyServiceImpl implements CompanyService{
@Override
public void addCompanyToLike(UserEntity user, Company company){
int incrementValue=1;
LikeEntity existingLike=user.getLikeEntities()
.stream()
.filter(le->le.getCompany().getName().equals(company.getName()))
.findFirst();
//rest of process
}
}
Here is the different java beans you will need to look at: UserEntity class:
@Document
@ViewIndexed(designDoc = "user")
class UserEntity implements Serializable{
@Field private String id;
@Reference
private List<LikeEntity> likeEntities=new ArrayList<LikeEntity>();
//Other attributes plus getters and setters:
}
LikeEntity class:
@Document
class LikeEntity implements serializable{
@Reference
private Company company;
@Reference
private Job job;
// other attributes plus getters and setters
}
As you see above, the LikeEntity class may contain any object liked by the user, it can be a company, a job or another object. Also the LikeEntity is stored only inside a user document as element of user’s list of likes and not independately in the database.It’s my choice of modelization because the LikeEntity won’t by used in other java beans of my application
Company:
@Document
@ViewIndexed(designDoc = "company")
class Company implements Serializable{
@Field private String id;
@Field private String name;
//Other attributes plus getters and setters
}
N.B: I’ve tried the next query inside UserRepository but it didn’t work:
UserRepository:
@Repository
@N1qlPrimaryIndexed
@N1qlSecondaryIndexed(indexName = "user")
public interface UserRepository extends CouchbaseRepository<UserEntity, String> {
@Query("SELECT b.*, likeEntity FROM #{#n1ql.bucket} AS b UNNEST b.likeEntities AS likeEntity WHERE b.id=$1 AND likeEntity.company.name=$2")
UserEntity findByLikedCompanyName(String idUser , String companyName);
}
I’m looking for your answers, and thank you so much in advance.
“deniswsrosa”, first of all I want to thank you a lot for your replication, second of all, I want to inform you that I have visited the link which you include in your resposne, it’s helpful, but it stills not responding to my need.
I want to have as type of the query return a UserEntity with the LikeEntity requested but flattened. For more clarification, here is an exmaple of the output I’m looking for:
Let’s suppose there is in the database the next user who I want to retreive using the N1QL query based on the name of the liked company ‘couchbase’:
{
'id':'123456',
'email':'test@test.com',
'username':'test1',
'likeEntity':
{
'likeId':'01010',
'company':{
'name':''couchBase'
}
}
//other attributes
//As you can see I want to replace the list of likeEntities by the only object LikeEntity
//whitch contains the liked company 'couchbase'
}
And if the user doesn’t exit in the database, I want to have a null Object:
Finally I want to thank you again for your reply, it means a lot for me.
“vsr1” Thanks for your answer, your query is the first one that I’ve tried before posting this topic in couchbase forum, but it didn’t work for me, I don’t know why, and here it is, as showen in the userRepository above, in the first post.
@Query("SELECT b.*, likeEntity FROM #{#n1ql.bucket} AS b UNNEST b.likeEntities AS likeEntity WHERE b.id=$1 AND likeEntity.company.name=$2")
UserEntity findByLikedCompanyName(String idUser , String companyName); }
“vsr1”, thank you so much for your quick reaction, I was wondering which sample document you want me to post. If you are talking about the userEntity document, I’ve just posted, as a replication to the first answer posted by “deniswsrosa”, an example of the output I’m looking for using a sample of userEntity document.
‘vsr1’, thank you, always, for your answers. On the one hand, the result of your query doesn’t correspond to the result I’m looking for. I would like to know if we can have a likeEntity flattened containing the company which name is ‘couchbase’, rather than this last one flattened as showen in your result.
On the other hand, I would like to know what does " USE KEYS kk01", in your query mean?
And thanks a lot in advance.
USE KEYS just example to work only this document, In your case you can remove it (so it uses indexscan based on predicates) and change bucket and conditions you want.
ANY v IN d.likeEntities SATISFIES v.company.name = "couchBase" END;
Above predicates check if any object in the array has matching condition. If there is one document is selected.
ARRAY v FOR v IN d.likeEntities WHEN v.company.name = "couchBase" END AS likeEntities
Above expression projection iterates through array constructs new array with matching entries only.
‘vsr1’, your second query gives the result I’m looking for, but the only thing I didn’t understand about it is the expression "USE KEYS “kk01”. I think I made a mistake by including the id of the user’s document inside it as property, it’s not the case in my couchBase server, the id is outside the document, so I will change the output sample, and I would like to know if the your query will work in this case.
Thank you so much for your help.
SELECT d.*,
ARRAY v FOR v IN d.likeEntities WHEN v.company.name = "couchBase" END AS likeEntities
FROM default AS d
WHERE d.username = "test1" AND ANY v IN d.likeEntities SATISFIES v.company.name = "couchBase" END;
id is outside document means it is document key, Your original query has b.id = $1, if you already know the document key in that case you can try this
SELECT d.*,
ARRAY v FOR v IN d.likeEntities WHEN v.company.name = "couchBase" END AS likeEntities
FROM default AS d USE KEYS $1
WHERE ANY v IN d.likeEntities SATISFIES v.company.name = "couchBase" END;
‘vsr1’, I have just read your last replication, now I understand, completly, your query, and my output sample was correct, there is no mistake in it.
Last but not least, I was wondering if you can help me writting your query using spring Expression Language so as to have a N1QL based query. Anyway, you helped me a lot, thank you so much.
Thank you so much, you helped me a lot, you can’t imagine how your answers were very helpful for me, and because of you I learnt new informations. All my respects great competency.