Have problem with Query with EXISTS and IN operator in postgres

Viewed 45

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'
}
0 Answers
Related