Combining scopes doesn't work

Question

I'm trying to combine three scopes in one (one scope uses the other two).

I want to get all videos which don't have certain categories and certain tags.

Video

class Video < ActiveRecord::Base
    self.primary_key = "id"
    has_and_belongs_to_many :categories
    has_and_belongs_to_many :tags

    scope :with_categories, ->(ids) { joins(:categories).where(categories: {id: ids}) }
    scope :excluded_tags, -> { joins(:tags).where(tags: {id: 15}) }

    scope :without_categories, ->(ids) { where.not(id: excluded_tags.with_categories(ids) ) }

end

But when I call

    @excluded_categories = [15,17,26,32,35,36,37]
    @videos = Video.without_categories(@excluded_categories)

I still get video which has tag 15.

The SQL query which server is firing looks like this

SELECT "videos"."video_id" FROM "videos" WHERE ("videos"."id" NOT IN (SELECT "videos"."id" FROM "videos" INNER JOIN "tags_videos" ON "tags_videos"."video_id" = "videos"."id" INNER JOIN "tags" ON "tags"."id" = "tags_videos"."tag_id" INNER JOIN "categories_videos" ON "categories_videos"."video_id" = "videos"."id" INNER JOIN "categories" ON "categories"."id" = "categories_videos"."category_id" WHERE "tags"."id" = $1 AND "categories"."id" IN (15, 17, 26, 32, 35, 36, 37)))  [["id", 15]]

Am I doing something wrong?

According to your query if your video has tag 15 but does not belong to categories with ids (15, 17, 26, 32, 35, 36, 37) it should be included to the results. I think that this might be the case. — geoandri, Sep 01 '16 at 11:58
I don't want to include it if it belongs to categories OR if it belongs to the tag 15 — Jakub Kohout, Sep 01 '16 at 12:02
Oh I can't see it now too the `AND`. There should be `OR`. But I don't know how to write it in a Rails way. — Jakub Kohout, Sep 01 '16 at 12:04
Related questions on the topic of OR'ing scopes: [here](http://stackoverflow.com/questions/3684311/rails-how-to-chain-scope-queries-with-or-instead-of-and), [here](http://stackoverflow.com/questions/37445203/combining-multiple-named-scopes-with-or), [here](http://stackoverflow.com/questions/1482940/combine-two-named-scopes-with-or-instead-of-and) and [here](http://stackoverflow.com/questions/16381619/using-or-with-queries-in-a-scope) — HeyZiko, Oct 26 '16 at 18:08

score 0 · Answer 1 · answered Aug 31 '16 at 15:53

0

 scope :excluded_tags, -> { joins(:tags).where.not(tags: {id: 15}) }

 scope :without_categories, ->(ids) { excluded_tags.with_categories(ids) }

answered Aug 31 '16 at 15:53

Thounder

315
2
14

geoandri · Accepted Answer · 2016-09-01T17:51:32.730

0

I think that you must use one scope for excluding categories and a second one for excluding tags and then combine them.

scope :without_categories, ->(ids) { joins(:categories).where.not(categories: {id: ids}) }
scope :without_tags, ->(ids) { joins(:tags).where.not(tags: {id: ids}) }

Then you can use

@excluded_categories = [1,2,3,4]
@excluded_tags = [1,2,3,4,5,6]
@videos = Video.without_categories(@excluded_categories).without_tags(@excluded_tags)

EDIT after comment

After seen the query because chained scopes are using AND the produced query cannot return the desired result. A new approach would be to create one scope only for this purpose.

scope :without_categories_tags, ->
(category_ids, tag_ids) { joins( :categories, :tags).
where('categories.id NOT IN (?) OR tags.id NOT IN (?)', category_ids, tag_ids)}

The you can use

@excluded_categories = [1,2,3,4]
@excluded_tags = [1,2,3,4,5,6]
@videos = Video.without_categories_tags(@excluded_categories,@excluded_tags)

edited Sep 01 '16 at 17:51

answered Sep 01 '16 at 12:24

geoandri

2,360
2
15
28

Still doesn't work and the SQL looks like this `SELECT "videos"."video_id" FROM "videos" INNER JOIN "categories_videos" ON "categories_videos"."video_id" = "videos"."id" INNER JOIN "categories" ON "categories"."id" = "categories_videos"."category_id" INNER JOIN "tags_videos" ON "tags_videos"."video_id" = "videos"."id" INNER JOIN "tags" ON "tags"."id" = "tags_videos"."tag_id" WHERE ("categories"."id" NOT IN (15, 17, 26, 32, 35, 36, 37)) AND ("tags"."id" != 15)` – Jakub Kohout Sep 01 '16 at 12:40
1

Edited my answer. – geoandri Sep 01 '16 at 13:39
It seems that we are on the good way, but now I get this error `PG::UndefinedTable: ERROR: missing FROM-clause entry for table "tag" LINE 1: ...be_categories.id NOT IN (15,17,26,32,35,36,37) OR ta...` I'm sorry but I'm not so good so far in raw sql – Jakub Kohout Sep 01 '16 at 17:13
Just add your actual table names . 'categories.id NOT IN (?) OR tags.id NOT IN (?)' should be. I edited the answer also – geoandri Sep 01 '16 at 17:49
It still doesn't work. I changed it and I still get the video which has tag 15. The query `SELECT "videos"."video_id" FROM "videos" INNER JOIN "categories_videos" ON "categories_videos"."video_id" = "videos"."id" INNER JOIN "categories" ON "categories"."id" = "categories_videos"."category_id" INNER JOIN "tags_videos" ON "tags_videos"."video_id" = "videos"."id" INNER JOIN "tags" ON "tags"."id" = "tags_videos"."tag_id" WHERE (categories.id NOT IN (15,17,26,32,35,36,37) OR tags.id NOT IN (15))` – Jakub Kohout Sep 02 '16 at 16:04

Combining scopes doesn't work

2 Answers2