I have items and tags table, which have many-to-many connection, I'm trying to select all items records, and set custom boolean field with true value if item have tag with some name, false otherwise.
Example:
SELECT *, EXISTS(
SELECT id, text FROM tags
INNER JOIN "itemsTags" ON "itemsTags"."itemId"=item.id AND "itemsTags"."tagId"=tags.id
WHERE text IN ('orcs', 'lol')) AS "hasTag" FROM "items" AS "item"
When I set only one value in IN operator it selects correctly and only one item record have true value, but if i set more, all records have true value.
And it doesn't matters exists the second value or not in the tags table.
I tried the next example, but result is the same
SELECT *, EXISTS(
SELECT * FROM tags
INNER JOIN "itemsTags" ON "itemsTags"."itemId"=item.id AND "itemsTags"."tagId"=tags.id
WHERE tags.text IN ('orcs', 'lol')) AS "hasTag" FROM "items" AS "item"
With the next query I selects all records and add extra fields coincidingTagsExists and coincidingTagsAmount. First extra field shows is there related tag exists, second - amount of tags relevant to query text: ogre or planet.
SELECT *, EXISTS(
SELECT * FROM tags INNER JOIN "itemsTags" ON "itemsTags"."itemId"=item.id AND "itemsTags"."tagId"=tags.id
WHERE tags.text IN ('ogre','planet')
) as "coincidingTagsExists", (SELECT COUNT(*) FROM tags INNER JOIN "itemsTags" ON "itemsTags"."itemId"=item.id AND "itemsTags"."tagId"=tags.id
WHERE tags.text IN ('ogre','planet')) as "coincidingTags" FROM "items" AS "item"
As I work with sequelize result was an array of objects, as we can see even when coincidingTagsAmount equals to 0, coincidingTagsExists fields still have true value:
{
id: 21,
name: 'shrek',
coincidingTagsExists: true,
coincidingTagsAmount: '1'
},
{
id: 23,
name: 'planet',
coincidingTagsExists: true,
coincidingTagsAmount: '1'
},
{
id: 24,
name: 'knight',
coincidingTagsExists: true,
coincidingTagsAmount: '0'
},
{
id: 25,
name: 'PolarMan',
coincidingTagsExists: true,
coincidingTagsAmount: '0'
}
But if I set in IN operator only ogre(...WHERE tags.text IN ('ogre')...), the result will be next:
{
id: 21,
name: 'shrek',
coincidingTagsExists: true,
coincidingTagsAmount: '1'
},
{
id: 23,
name: 'planet',
coincidingTagsExists: false,
coincidingTagsAmount: '0'
},
{
id: 24,
name: 'knight',
coincidingTagsExists: false,
coincidingTagsAmount: '0'
},
{
id: 25,
name: 'PolarMan',
coincidingTagsExists: false,
coincidingTagsAmount: '0'
}