PSQLException: ERROR: syntax error at or near “.”

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.

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))

Edit 2

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))"
    )

Answer

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)"+