I have table called employee_hashtags like this :

 ---- --------- --------------- 
| id | emp_id  |  hashtag_ids  |
 ---- --------- --------------- 
|  1 |   1     |  [1, 4, 5]    |
 ---- --------- --------------- 

I want to query where hashtag_ids has value that i want to search. It's success, but, the result on hashtag_ids was contain all value in that field.

here's my result :

[
   {
        "emp_id": 1,
        "hashtag_ids": [
            1,
            4
        ],
        "user": {
            "emp_full_name": "Agam",
            "emp_email": "[email protected]",
            "emp_phone_no": null,
            "preferred_work_type": null
        },
        "hashtag": []
    }
]

the problems is the hashtag field can't find the value because the hashtag_ids was in array.

this is the result was i expected :

[
   {
        "emp_id": 1,
        "hashtag_ids": 1,
        "user": {
            "emp_full_name": "Agam",
            "emp_email": "[email protected]",
            "emp_phone_no": null,
            "preferred_work_type": null
        },
        "hashtag": {
            "name": "NodeJs"
        }
   }
]

this is my query, i'm using sequelize here :

let employeeHashtagQuery = 1
const users = await EmployeeHashtag.findAll({
        attributes: ['emp_id', 'hashtag_ids'],
        where: Sequelize.fn('JSON_CONTAINS', Sequelize.col('hashtag_ids'), Sequelize.cast(`${employeeHashtagQuery}`, 'CHAR CHARACTER SET utf8')),
        limit: parseInt(limit),
        offset: (page - 1) * limit,
        include: [
          {
            model: UserEmployee,
            as: 'user',
            attributes: ['emp_full_name', 'emp_email', 'emp_phone_no', 'preferred_work_type']
          },
          {
            model: Hashtag,
            as: 'hashtag',
            attributes: ['id', 'name', 'hashtag_group_id']
          }
        ]
      })

Is that could be resolved ?

Please let me know if you need more information if it's still not enough

CodePudding user response：

use the IN operator for a matching array of value

SELECT * FROM EmployeeHashtag WHERE hashtag_ids IN ('4','3')

CodePudding user response：

Change your employee-hashtag mapping id to id mapping rather than id to array mapping. your table should look like this:

 ---- --------- --------------- 
| id | emp_id  |  hashtag_ids  |
 ---- --------- --------------- 
|  1 |   1     |       1       |
 ---- --------- --------------- 
|  1 |   1     |       4       |
 ---- --------- --------------- 

Then use sequelize association that you have used above.

Otherwise, you'll have to loop through the array yourself to create the desired object.