WhereNotIn in WhereHas not working

[D-Marc1]

I have a Song model and I'm trying to filter out songs by category tags, in both a white and blacklist.

const requestTags = [3];
const requestNotTags = [2, 4];

const resultQuery = Song.query()
  .select('id', 'title', 'duration', 'type')
  .preload('categories', (query) => {
    query.select('name');
  })
  .whereHas('categories', (query) => {
    if (requestTags.length > 0) {
      query.whereIn('categories.id', requestTags); // This works perfectly fine
    }

    if (requestNotTags.length > 0) {
      query.whereNotIn('categories.id', requestNotTags); // This doesn't work
    }
  });

I initially thought this was a bug when I asked the question, but now understand it's obviously not. Anyway, there's more discussion about here with details.

Also, this near identical scenario was asked in Laravel Eloquent, but the answer didn't seem to work for me. Does anyone have any clue to get the desired result with whereNotIn()? Using whereIn() works fine.

[thetutlage]

You have to be more explicit when you say "This doesn't work"? Also, you have to narrow down where the issue is.

If there is a bug in the query builder, then it will produce the wrong SQL query. So first thing is to debug and check the SQL query.

[D-Marc1]

All of the details are here in our discussion when I asked it as a bug, because I thought it was one #1677. Since it's the same in Laravel, it's definitely not a bug. Basically, the gist is that literally doesn't remove it in this example. I post more examples in that issue. If I add whereNotIn() to preload, then it works if there's only one item in the array, but if there's multiple, then it merely removes the item from the array. Let me know if any more clarification is need, and I'd be happy to.

I truly appreciate your consistently quick replies btw!

[thetutlage]

The parameter to find something is a bug or not is weird (Since it's the same in Laravel, it's definitely not a bug).

As I also mentioned in the earlier thread. The first step is to check the SQL generated by the query builder. If the SQL is correct, it means the query builder is behaving correctly and it is a question about expectations.

To avoid too much back and forth, can you please share a repo with the bare minimum code to help me reproduce the issue?

[D-Marc1]

I created a repo with the bare minimum code here https://github.com/D-Marc1/songs-adonis-test. I created the Song and Category models, migrations, controllers and the route to get the song. I also created the many-to-many relationship with it in that repo.

As for the generated SQL, I already posted that here #1677 (comment). We're in agreement that it's definitely not a bug anyway though. I do 100% understand why I can't achieve my intended result. I just need help figuring out how to get it to work.

Please let me know if you need any more information from my end, and I'd be happy to oblige.

[thetutlage]

I just tried your code with following data set.

Songs table

id	title	type	duration	time_start_repeat	created_at	updated_at
1	Numb / Encore	a	11:22:16.430411	0	2020-10-29 11:22:16.430411+05:30	2020-10-29 11:22:16.430411+05:30

Songs categories table

id	song_id	category_id	created_at	updated_at
1	1	1	2020-10-29 11:23:01.695763+05:30	2020-10-29 11:23:01.695763+05:30
2	1	2	2020-10-29 11:23:01.695763+05:30	2020-10-29 11:23:01.695763+05:30

Categories table

id	name	created_at	updated_at
1	Rock	2020-10-29 11:21:12.508325+05:30	2020-10-29 11:21:12.508325+05:30
2	Rap	2020-10-29 11:21:12.508325+05:30	2020-10-29 11:21:12.508325+05:30

Next I remove the preload calls to simplify the reproduction.

You expect that if you fetch a song with tags=[1] and notTags=[2], the result should not contain any songs right? If yes, following is the query you have to write.

    const query = Song.query().select("songs.id", "title", "duration", "type");
    if (requestTags.length > 0) {
      query.whereHas("categories", (query) => {
        query.whereIn("categories.id", requestTags);
      });
    }

    if (requestNotTags.length > 0) {
      query.whereDoesntHave("categories", (query) => {
        query.whereIn("categories.id", requestNotTags);
      });
    }

    const resultQuery = await query;

And it produce the following SQL statement.

SELECT
	"songs"."id",
	"title",
	"duration",
	"type"
FROM
	"songs"
WHERE
	EXISTS (
		SELECT
			*
		FROM
			"categories"
			INNER JOIN "category_song" ON "categories"."id" = "category_song"."category_id"
		WHERE
			"categories"."id" IN(?)
			AND "songs"."id" = "category_song"."song_id")
		AND NOT EXISTS (
			SELECT
				*
			FROM
				"categories"
				INNER JOIN "category_song" ON "categories"."id" = "category_song"."category_id"
			WHERE
				"categories"."id" IN(?)
				AND "songs"."id" = "category_song"."song_id")

The preload code can be the same as it as earlier.

And finally, for complex SQL queries, you have to start with SQL first and then write the equivalent Javascript code. At the end of the day its the SQL that has to be correct

[D-Marc1]

@thetutlage Seems to work like a charm for me. You seriously are the man! I can't thank you enough for being so helpful on this forum.

That's a good point about thinking of complex queries in terms of SQL also.