Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

not_eq doesn't seems to work with a relation #1438

Open
Maxhou00 opened this issue Sep 12, 2023 · 1 comment
Open

not_eq doesn't seems to work with a relation #1438

Maxhou00 opened this issue Sep 12, 2023 · 1 comment

Comments

@Maxhou00
Copy link

Maxhou00 commented Sep 12, 2023

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

@Maxhou00 Maxhou00 changed the title not_eq doesn't seems to work with not_eq doesn't seems to work with a relation Sep 12, 2023
@mkcosta
Copy link

mkcosta commented Sep 12, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants