whereHas not working?

I’m trying to make a query that return the quizzes of one class only if execution_back_status is equal to “Enviado Perguntas”.

But i’m receiving records different of “Enviado Perguntas”

What i try:

let quizAbertos = await Class.query()

    .with('quizzes')

    .whereHas('quizzes', builder => {

      builder.where('student_id', idEstudante)

      builder.where('execution_back_status', 'Enviado Perguntas')

     })

    .fetch()

    quizAbertos = quizAbertos.toJSON()

    console.log(JSON.stringify(quizAbertos))

My console.log():

[
  {
    "id": 1,
    "code": "ING-NOT-2020",
    "description": "Inglês Noturno 2020",
    "start_date": "2020-01-06T03:00:00.000Z",
    "end_date": "2020-03-01T03:00:00.000Z",
    "period": "Noturno",
    "language": "Inglês",
    "status": false,
    "user_id": 9,
    "created_at": "2020-01-06 09:46:30",
    "updated_at": "2020-02-06 08:10:33",
    "language_substring": "US",
    "quizzes": [
      {
        "id": 819,
        "sequence": null,
        "student_id": 1,
        "class_id": 1,
        "book_id": 1,
        "book_unit_id": 1,
        "quiz_id": 828,
        "percentage_correct": 40,
        "review": false,
        "execution_back_status": "Reprovado",
        "user_id": null,
        "created_at": "2020-02-06 10:45:25",
        "updated_at": "2020-02-06 11:16:23"
      },
      {
        "id": 820,
        "sequence": null,
        "student_id": 1,
        "class_id": 1,
        "book_id": 1,
        "book_unit_id": 1,
        "quiz_id": 829,
        "percentage_correct": null,
        "review": false,
        "execution_back_status": "Enviado Perguntas",
        "user_id": null,
        "created_at": "2020-02-06 11:16:52",
        "updated_at": "2020-02-06 11:16:52"
      }
    ]
  }
]

As you can see, i have results with “execution_back_status”: Reprovado, i need only with value “Enviado Perguntas”

Why this is happening?

In Class Model i have:

quizzes () {

   return this.hasMany('App/Models/StudentQuizHistorics')

  }
2 Likes

You need to apply filter when call .with(). Like :

let quizAbertos = await Class.query()
    .with('quizzes', builder => {
      builder.where('student_id', idEstudante)
      builder.where('execution_back_status', 'Enviado Perguntas')
     })
  .fetch()
...

In your case, whereHas() return all quizAbertos where one of quizzes condition is true (and in the result, it includes all other quizzes where the condition is false)

I put this way but is not applying neither the first neither the secod where, is returning all classes andi’m receive a empty array in quizzes[]

quizzes is empty because one of the 2 conditions is false.
If you don’t want quizAbertos where quizzes is empty I found this way :

// Testing code
var result = User.query()
    .with("posts", builder => {
      // Filter on the posts array
      builder.where("cond1", "2");
      builder.where("cond2", "3");
    })
    .whereHas("posts", builder => {
      // Filter user
      builder.where("cond1", "1");
      builder.where("cond2", "3");
    })
    .fetch();

I don’t think it’s the best way, it’s the only solution I found

2 Likes

My two conditions are true, if i do a select in database i get the record that i expect:

If i do a console.log(idEstudante) i have the number 1

with a builder in with and whereHas work, i don’t know why… but thanks

1 Like