$db->expr()->isMemberOf() make to 'value member of json_array' show a Syntax Error #7217

New Issue

admin · 2026-01-22T15:47:14+01:00

admin commented

2026-01-22 15:47:14 +01:00

Originally created by @siburuxue on GitHub (Sep 3, 2023).

the php code:

if (!empty($param['dKey3'])) {
    foreach ($param['dKey3'] as $key) {
        $and = $db->expr()->andX();
        $and->add($db->expr()->isMemberOf("'" . $key . "'", "(t.dKey3)"));
        $db->andWhere($and);
    }
}

and then show me a Syntax Error:

{
  "message": "Uncaught PHP Exception Doctrine\\ORM\\Query\\QueryException: \"[Syntax Error] line 0, col 62: Error: Expected =, <, <=, <>, >, >=, !=, got 'MEMBER'\" at /Users/zp/Desktop/project/php/symfony-webapp-example/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 34",
  "context": {
    "exception": {
      "class": "Doctrine\\ORM\\Query\\QueryException",
      "message": "[Syntax Error] line 0, col 62: Error: Expected =, <, <=, <>, >, >=, !=, got 'MEMBER'",
      "code": 0,
      "file": "/Users/zp/Desktop/project/php/symfony-webapp-example/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:34",
      "previous": {
        "class": "Doctrine\\ORM\\Query\\QueryException",
        "message": "SELECT t FROM App\\Entity\\TestDict t WHERE t.isDel = 0 AND '1' MEMBER OF (t.dKey3) AND '4' MEMBER OF (t.dKey3) ORDER BY t.id DESC",
        "code": 0,
        "file": "/Users/zp/Desktop/project/php/symfony-webapp-example/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:23"
      }
    }
  },
  "level": 500,
  "level_name": "CRITICAL",
  "channel": "request",
  "datetime": "2023-09-03T18:33:42.640274+00:00",
  "extra": {
    "token": "e67QB4eVmEi5g36OEi3tYXaDQDEMigbs",
    "post": {
      "type": "",
      "dKey": "",
      "dValue": "",
      "dKey3": [
        "1",
        "4"
      ],
      "uKey": "",
      "remark": "",
      "page": "1",
      "limit": "10"
    },
    "get": [],
    "route_params": []
  }
}

but when run the sql in the database, it's working!

SELECT t.*
FROM test_dict t
WHERE t.is_del = 0 AND '1' MEMBER OF (t.d_key3) AND '4' MEMBER OF (t.d_key3)
ORDER BY t.id DESC

the database struct:

-- auto-generated definition
create table test_dict
(
    id          int auto_increment comment ' 主键 '
        primary key,
    type        varchar(255)  not null comment '字典名称',
    d_key       varchar(255)  not null,
    d_key1      int           null,
    d_key2      varchar(25)   null,
    d_key3      json          null,
    d_value     varchar(255)  not null,
    u_key       varchar(255)  not null comment '唯一标识',
    remark      varchar(255)  not null comment '备注',
    is_del      int default 0 not null comment '0否1是',
    create_user int           not null comment ' 添加人 ',
    update_user int           not null comment ' 修改人 ',
    create_time int           not null comment ' 添加时间 ',
    update_time int           not null comment ' 修改时间 '
);

INSERT INTO symfony_example.test_dict (id, type, d_key, d_key1, d_key2, d_key3, d_value, u_key, remark, is_del, create_user, update_user, create_time, update_time) VALUES (11, '测试数据', '1', 4, '["1","2","3"]', '["1", "2", "4"]', 'fdafasd', 'fdaafd', 'fdafdsa', 0, 1, 1, 1693745586, 1693745823);

Originally created by @siburuxue on GitHub (Sep 3, 2023). the php code: ```php if (!empty($param['dKey3'])) { foreach ($param['dKey3'] as $key) { $and = $db->expr()->andX(); $and->add($db->expr()->isMemberOf("'" . $key . "'", "(t.dKey3)")); $db->andWhere($and); } } ``` and then show me a Syntax Error: ```json { "message": "Uncaught PHP Exception Doctrine\\ORM\\Query\\QueryException: \"[Syntax Error] line 0, col 62: Error: Expected =, <, <=, <>, >, >=, !=, got 'MEMBER'\" at /Users/zp/Desktop/project/php/symfony-webapp-example/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 34", "context": { "exception": { "class": "Doctrine\\ORM\\Query\\QueryException", "message": "[Syntax Error] line 0, col 62: Error: Expected =, <, <=, <>, >, >=, !=, got 'MEMBER'", "code": 0, "file": "/Users/zp/Desktop/project/php/symfony-webapp-example/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:34", "previous": { "class": "Doctrine\\ORM\\Query\\QueryException", "message": "SELECT t FROM App\\Entity\\TestDict t WHERE t.isDel = 0 AND '1' MEMBER OF (t.dKey3) AND '4' MEMBER OF (t.dKey3) ORDER BY t.id DESC", "code": 0, "file": "/Users/zp/Desktop/project/php/symfony-webapp-example/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:23" } } }, "level": 500, "level_name": "CRITICAL", "channel": "request", "datetime": "2023-09-03T18:33:42.640274+00:00", "extra": { "token": "e67QB4eVmEi5g36OEi3tYXaDQDEMigbs", "post": { "type": "", "dKey": "", "dValue": "", "dKey3": [ "1", "4" ], "uKey": "", "remark": "", "page": "1", "limit": "10" }, "get": [], "route_params": [] } } ``` but when run the sql in the database, it's working! ```sql SELECT t.* FROM test_dict t WHERE t.is_del = 0 AND '1' MEMBER OF (t.d_key3) AND '4' MEMBER OF (t.d_key3) ORDER BY t.id DESC ``` the database struct: ```sql -- auto-generated definition create table test_dict ( id int auto_increment comment ' 主键 ' primary key, type varchar(255) not null comment '字典名称', d_key varchar(255) not null, d_key1 int null, d_key2 varchar(25) null, d_key3 json null, d_value varchar(255) not null, u_key varchar(255) not null comment '唯一标识', remark varchar(255) not null comment '备注', is_del int default 0 not null comment '0否1是', create_user int not null comment ' 添加人 ', update_user int not null comment ' 修改人 ', create_time int not null comment ' 添加时间 ', update_time int not null comment ' 修改时间 ' ); INSERT INTO symfony_example.test_dict (id, type, d_key, d_key1, d_key2, d_key3, d_value, u_key, remark, is_del, create_user, update_user, create_time, update_time) VALUES (11, '测试数据', '1', 4, '["1","2","3"]', '["1", "2", "4"]', 'fdafasd', 'fdaafd', 'fdafdsa', 0, 1, 1, 1693745586, 1693745823); ```

admin added the Question label 2026-01-22 15:47:14 +01:00

admin closed this issue

2026-01-22 15:47:15 +01:00

admin commented

2026-01-22 15:47:16 +01:00

@derrabus commented on GitHub (Sep 4, 2023):

The DQL operator MEMBER OF is meant to be used on to-many associations and does not translate to the SQL operator MEMBER OF some SQL dialects support for JSON fields.

@derrabus commented on GitHub (Sep 4, 2023): The DQL operator `MEMBER OF` is meant to be used on to-many associations and does not translate to the SQL operator `MEMBER OF` some SQL dialects support for JSON fields.

admin commented

2026-01-22 15:47:16 +01:00

@siburuxue commented on GitHub (Sep 4, 2023):

can I define a custom function for the dql to translate the member of ?
And how can I do it?

@siburuxue commented on GitHub (Sep 4, 2023): can I define a custom function for the dql to translate the member of ? And how can I do it?

admin referenced this issue

2026-01-22 16:07:20 +01:00

[PR #7217] Add test for GH issue 7212 #10376

Sign in to join this conversation.