Hi, i am trying to write code on spring boot to use join query.
This is what i have: @Query(
“SELECT * FROM #{#n1ql.bucket} AS f LEFT OUTER JOIN #{#n1ql.bucket} AS c ON f.category = meta( c ).id WHERE META(f).id = $1”
)
List<?> findByQuery(String id);
@Query("SELECT 0 as “+TemplateUtils.SELECT_CAS +”, ‘’ as "’ as “+TemplateUtils.SELECT_ID+” , MAX(…
public class TemplateUtils {
public static final String SELECT_ID = “__id”;
public static final String SELECT_CAS = “__cas”;See https://www.couchbase.com/forums/t/unable-to-use-n1ql-query-in-query-annotation-with-max-function-in-spring-data-couchbase-4-1-5/29776
Hey @mreiche@eln
I am trying to work out a join query on Spring boot. I have two documents in the same bucket…one being message1 and other being user1.(‘1’ is the user id). message1 document has a field called user_id.
I got to join these two documents based on common user_id.
my query goes like- @Query(“Select *,META(#{n1ql.bucket}).id AS __id, META(#{n1ql.bucket}).cas AS __cas FROM #{n1ql.bucket} AS d1 JOIN #{n1ql.bucket} AS d2 ON KEYS ‘user’ || d1.user_id where d1.docType=$1”)
List<?> getMsgWithUsrPrfl(String docType)
This is giving me the following exception - com.couchbase.client.core.error.ParsingFailureException: Parsing of the input failed
Thanks for responding @mreiche .
A similar query is working on couchbase query bench but doesn’t work in java.
I am unable to find out where am I syntactically going wrong.
would be great if you could highlight something here.
@arushi I do not have expertise in the Java SDK, so I probably cannot help further here, but it occurred to me that having a typo implies the query you showed was retyped rather than cut-and-pasted from your actual code, so there is potential for other typos, and possibly you corrected the syntax error reflexively when you originally retyped the query into the posting but that correction is not in the actual code. Hopefully someone from the JSDK side can help further if that isn’t the issue.
When I write - @Query(“Select *,META(#{#n1ql.bucket}).id AS __id, META(#{#n1ql.bucket}).cas AS __cas FROM #{#n1ql.bucket} where d1.docType=$1”)…this works
But when I write
2) @Query(“Select *,META(#{#n1ql.bucket}).id AS __id, META(#{#n1ql.bucket}).cas AS __cas FROM #{#n1ql.bucket} AS d1 where d1.docType=$1”)…It gives ParsingFailureException
Based on previous post, May not have properly translated check out. enable tracing in client side and see what query it generated. (Is it making double AS (i.e. bucket AS bucket AS d1 ) )
Also try replace #{n1ql.bucket} with actual bucket and see if that works.
Another non related is.
Once you Alias from clause, META argument must be alias (META(alais) …) otherwise you will not get right values.
“A similar query is working on couchbase query bench but doesn’t work in java.”
That would indicate they are different. The difference is what is causing the issue.
Amazing! Just writing meta().id works fine.
So now- @Query(“Select *,META().id AS __id, META().cas AS __cas FROM #{#n1ql.bucket} AS d1 where d1.docType=$1”) works
Adding JOIN #{#n1ql.bucket} AS d2 ON KEYS ‘user’ || d1.user_id join clause gives parsing exception yet again.
JOIN query will give Ambiguous error without argument to META(). You need to give one of the alais
@Query(“Select *,META(d1).id AS __id, META(d1).cas AS __cas FROM #{n1ql.bucket} AS d1 JOIN #{n1ql.bucket} AS d2 ON KEYS ‘user’ || d1.user_id where d1.docType=$1”)