not_eq doesn't seems to work with a relation

[Maxhou00]

Hello,

I feel like there is a problem on the way to create the SQL query for a not_eq ransack query with a relation. For a simple not_eq ransack params we have a simple SQL query which is easy to read and works well.

=>User.ransack(g: { '0': { firstname_not_eq: 'foo' } }).result.to_sql
>SELECT "users".* FROM "users" WHERE "users"."firstname" != 'foo'

but if I want to find a not equal value in a relation of my user model it's starting to be complicated. It will generate a weird SQL query which is, honestly, difficult to read because of the triple negation NOT IN ... NOT ... !=

=> User.ransack(g: { '0': { comments_upvote_not_eq: '1.0' } }).result.to_sql
> SELECT "users".* FROM "users" WHERE "users"."id" NOT IN (SELECT "comments"."user_id" FROM "comments" WHERE "comments"."user_id" = "users"."id" AND NOT ("comments"."upvote" != '1.0'))

With my few knowledge I would have expected an easier query like the first one, but I guess it's a generic approach for letting the possibility to create more complex combination.

The problem is this query seems to be wrong in the result. Here is a comparison between using ransack and activerecord.

=>  User.joins(:comments).where("comments.article_id = 1 AND comments.upvote != '1.0'").count

SELECT COUNT(*) FROM "users" INNER JOIN "comments" ON "comments"."user_id" = "users"."id" WHERE (comments.article_id = 1 AND comments.upvote != '1.0')
> 20

=> User.ransack(g: { '0': { comments_article_id_eq: 1, comments_upvote_not_eq: '1.0' } }).result.size

SELECT COUNT(*) FROM "users" LEFT OUTER JOIN "comments" ON "comments"."user_id" = "users"."id" WHERE ("comments"."article_id" = 1 AND "users"."id" NOT IN (SELECT "comments"."user_id" FROM "comments" WHERE "comments"."user_id" = "users"."id" AND NOT ("comments"."upvote" != '1.0')))
> 0

It's weird because the SQL query look correct but the triple negation doesn't make it easy to understand or to find the difference between the first query and the second one.

I created a demo where you can test it yourself. The test can seems odd because it would return 20 times the same user but the problem is here.

Thank you for reading and taking a look to it.

Rails version: 7.0.2

Ruby version: 3.1.2

Ransack version: 4.0.0

[mkcosta]

Same issue here.

In the usual model->taggings->tag scenario i get an inner query which doesn't work with any negated predicate:

puts StockItem.distinct.ransack({ "sku_tags_name_not_in"=>"tag1" }).to_sql

Results in an inner query with a triple negation SQL which doesn't filter correctly the records:

SELECT DISTINCT "stock_items".* FROM "stock_items" LEFT OUTER JOIN "skus" ON "skus"."id" = "stock_items"."sku_id" WHERE "stock_items"."id" NOT IN (SELECT "taggings"."taggable_id" FROM "taggings" INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE "taggings"."taggable_id" = "stock_items"."id" AND NOT ("tags"."name" NOT IN ('tag1'))))

I was expecting a negated version of the positive predicate, which would had worked fine:

SELECT DISTINCT "stock_items".* FROM "stock_items" LEFT OUTER JOIN "skus" ON "skus"."id" = "stock_items"."sku_id" LEFT OUTER JOIN "taggings" ON "taggings"."taggable_type" = 'Sku' AND "taggings"."taggable_id" = "skus"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE  "tags"."name" NOT IN ('tag1'))

Rails: 7.0.8.4

Ruby: 3.2.2

Ransack: 4.2.1