mybatis-plus 数组包含查询（postgreSql）

我们都知道PostgreSQL数组类型字段查询可以使用 ANY/SOME/ALL 关键字进行筛选。

匹配结果在数组中存在表达式： WHERE expression operator ANY (subquery) WHERE expression operator SOME (subquery) WHERE expression operator ALL(subquery)

附数组操作符:

Operator Description Example Result = equal ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] t <> not equal ARRAY[1,2,3] <> ARRAY[1,2,4] t < less than ARRAY[1,2,3] < ARRAY[1,2,4] t > greater than ARRAY[1,4,3] > ARRAY[1,2,4] t <= less than or equal ARRAY[1,2,3] <= ARRAY[1,2,3] t >= greater than or equal ARRAY[1,4,3] >= ARRAY[1,4,3] t @> contains ARRAY[1,4,3] @> ARRAY[3,1] t <@ is contained by ARRAY[2,7] <@ ARRAY[1,7,4,2,6] t && overlap (have elements in common) ARRAY[1,4,3] && ARRAY[2,1] t || array-to-array concatenation ARRAY[1,2,3] || ARRAY[4,5,6] {1,2,3,4,5,6} || array-to-array concatenation ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] { {1,2,3},{4,5,6},{7,8,9}} || element-to-array concatenation 3 || ARRAY[4,5,6] {3,4,5,6} || array-to-element concatenation ARRAY[4,5,6] || 7 {4,5,6,7}

数组函数：

Function Return Type Description Example Result array_append(anyarray, anyelement) anyarray append an element to the end of an array array_append(ARRAY[1,2], 3) {1,2,3} array_cat(anyarray, anyarray) anyarray concatenate two arrays array_cat(ARRAY[1,2,3], ARRAY[4,5]) {1,2,3,4,5} array_ndims(anyarray) int returns the number of dimensions of the array array_ndims(ARRAY[[1,2,3], [4,5,6]]) 2 array_dims(anyarray) text returns a text representation of arrays dimensions array_dims(ARRAY[[1,2,3], [4,5,6]]) [1:2][1:3] array_fill(anyelement, int[], [, int[]]) anyarray returns an array initialized with supplied value and dimensions, optionally with lower bounds other than 1 array_fill(7, ARRAY[3], ARRAY[2]) [2:4]={7,7,7} array_length(anyarray, int) int returns the length of the requested array dimension array_length(array[1,2,3], 1) 3 array_lower(anyarray, int) int returns lower bound of the requested array dimension array_lower([0:2]={1,2,3}::int[], 1) 0 array_prepend(anyelement, anyarray) anyarray append an element to the beginning of an array array_prepend(1, ARRAY[2,3]) {1,2,3} array_to_string(anyarray, text [, text]) text concatenates array elements using supplied delimiter and optional null string array_to_string(ARRAY[1, 2, 3, NULL, 5], ,, *) 1,2,3,*,5 array_upper(anyarray, int) int returns upper bound of the requested array dimension array_upper(ARRAY[1,8,3,7], 1) 4 string_to_array(text, text [, text]) text[] splits string into array elements using supplied delimiter and optional null string string_to_array(xx~^~yy~^~zz, ~^~, yy) {xx,NULL,zz} unnest(anyarray) setof anyelement expand an array to a set of rows unnest(ARRAY[1,2]) 1 2 (2 rows)

在mybatis-plus中如果我们要查询我们可以这样写：

<select id="getUserStarShop" resultMap="BaseResultMap">
        SELECT A.*,B.name AS clientName
        FROM user A
        LEFT JOIN user_star B ON B.shop_app=A.shop_app
        WHERE A.status=1
        <if test="clientId != null and clientId != ">
            AND A.shop_app=#{shopApp} OR array_to_string(A.shop_apps, ,) LIKE CONCAT(%,#{shopApp},%)
        </if>
        <if test="status != null and status !=  or status == 0 " >
            AND A.status=#{status}
        </if>
        order by sort DESC
    </select>