I'm using Java 8, Spring Boot, and PostgreSQL.

I have the following query

@Query(value = "select mc from MasterCourse mc "  
            "where mc.institution.guid = :institutionGuid or mc.consortium = true "  
            "and (:searchStr in (mc.tags) "  
            "or lower(mc.name) like concat('%', lower(:searchStr),'%') "  
            "or lower(mc.description) like concat('%', lower(:searchStr),'%') "  
            "or (:searchStr) in (mc.categories) "  
            "or (:searchStr) in (mc.levels)" 
            "or mc.authorGuid in (:authorGuids))"

and it is giving me an error PSQLException: ERROR: syntax error at or near "."

I can't seem to figure out what is causing this. It does not appear to be any reserved words.

Any help would be appreciated. If you need additional information, please let me know and I can get that asap.

EDIT:

So I turned on debug logs and got this executed SQL. It looks like it's not liking my and (:searchStr in (mc.tags). mc.tags appears to just be returning a (.). I'm guessing this is because of the fact that tags is a list of objects, not just strings.
I'm trying to match the searchStr on the "name" field of the tags. Any ideas on how to achieve this?

where mastercour0_1_.institution_id=institutio1_.id and 
mastercour0_.id=tags2_.master_course_id and tags2_.tag_id=tag3_.id and mastercour0_.id=categories4_.master_course_id and 
categories4_.category_id=category5_.id and 
mastercour0_.id=levels6_.master_course_id and 
levels6_.level_id=level7_.id and (institutio1_.guid=? or mastercour0_.consortium=true 
and (? in (.)) 
or lower(mastercour0_.name) like ('%'||lower(?)||'%') 
or lower(mastercour0_.description) like ('%'||lower(?)||'%') 
or ? in (.) or ? in (.) or mastercour0_.author_guid in (null))

EDIT2: I resolved this with the following code. The answer provided on this question partially resolved it by finding a missing space on a line break. The comments on my question helped point me in the right direction of some joins.

@Query("select mc from MasterCourse mc "  
            "left join mc.tags t "  
            "left join mc.categories cat "  
            "left join mc.levels l "  
            "where (mc.institution.guid = :institutionGuid or mc.consortium = true) "  
                "and (CONCAT('%',lower(:searchStr),'%') like lower(t.friendly) "  
                "or CONCAT('%',lower(:searchStr),'%') like lower(mc.name) "  
                "or CONCAT('%',lower(:searchStr),'%') like lower(mc.description) "  
                "or CONCAT('%',lower(:searchStr),'%') like lower(cat.friendly) "  
                "or CONCAT('%',lower(:searchStr),'%') like lower(l.friendly) " 
                "or mc.authorGuid in (:authorGuids))"
    )

CodePudding user response：

1. concat only takes two arguments You can use the || operator instead of concat function, but that's personal preference.
2. you're missing a space at the end of the second to the last line (mc.levels)"