2

I am following instructions from Bookmarks tutorial, and i have a problem with one of the queries.

I have baked all models from my database (like in tutorial) and now i want to prepare custom finder.

I have two tables Academic Teachers and Evaluations

CREATE TABLE evaluations (
  ID                        int(10) NOT NULL AUTO_INCREMENT, 
  academic_teacher_ID int(10) NOT NULL, 
  framework_ID           int(10) NOT NULL, 
  protocol_ID   int(10) NOT NULL, 
  final_note                     DOUBLE, 
  room                      varchar(255), 
  PRIMARY KEY (ID)) ;
CREATE TABLE academic_teachers (
  ID         int(10) NOT NULL AUTO_INCREMENT, 
  name       varchar(255) NOT NULL, 
  surname   varchar(255) NOT NULL, 
  national_identification_number      int(10) NOT NULL, 
  occupation varchar(4), 
  degree      varchar(255), 
  department  varchar(255), 
  PRIMARY KEY (ID)) ;

I want to find teachers, who at their last evaluation received a low note.

public function findLowNotes(Query $query, array $options)
{
return $this->find()
->distinct(['AcademicTeachers.id'])
->matching('Evaluations', function ($q) {

    return $q->where(['Evaluations.final_note' < 3]);
});

But it wil find all the teachers who ever had any bad note. How should it be? Shall I somewhere use one of the ways of retrieving last ID that I found? Or there is a more clever way that will surely work? I am much confused how to combine it all together.

And - is it possible to join here with that final_note, so I could paginate it along with that teacher's data?

Yours sincerely, Milven

Milven
  • 21
  • 2
  • No time for an answer right now, but you're pretty much looking for **http://stackoverflow.com/questions/30241975/how-to-limit-contained-associations-per-record-group/30270675#30270675** with an extra condition, whether you implement it as an extra association, or directly in the finder (which should not return a new query btw, but modify the passed one). – ndm Jan 25 '17 at 20:22
  • Thanks for your advice, but I have some problem, I have written 'bout it below. Short film: i have not only foreign key,, but also join table to seperate evaluated teacher form the ones who evaluate him. I suppose that can break the finder, so the one I have wrote is useless anyway. Or maybe should I change model of evaluation? There should be not only belongToMany, but also hasOne. Will it work if I add it manually? – Milven Jan 27 '17 at 15:43
  • If you think that further associations could possibly be affecting the task, then please add them into your question. It's important that you always show the full picture! Also I'd suggest to not accept the answer given below, as it does not address the actual problem/question (which to me still sounds like a [tag:greatest-n-per-group] task), but rather a syntax error, which when fixed, shouldn't really change the result. – ndm Jan 27 '17 at 20:20
  • OK, I suppose that the best way is to simply give an whole project by GitHub: [https://github.com/marcinmateuszwisniewski/hospit3 Also, DDL script for database used: [http://pastebin.com/EuagrRt9 And yes, that answer has been accepted unnecessarily - I suspected, that because there were no errors, when function with finder was called, It's OK, then I saw that answer, and when I finally tried adding records through baked templates, discovered It doesn;t work at all. – Milven Jan 28 '17 at 13:14
  • Ok, I have found way to make finder work, all I had to do was to add all other associations - Cake baked only ones for the join table, all the rest was non-existent. I didn't expected that, I thoght that Cake looks at table and sets ALL associations based on constraints in database. Anyway - let's forget about the greatest-n-per-group problem. Now I want to paginate my query and show not only personal data, but also the final note received by those teachers. Meanwhile, I had started project again, so that old link to GitHub aren;t updated files. – Milven Jan 29 '17 at 16:38
  • Tried this: `$academicTeachers = $this->paginate($this->AcademicTeachers->find('lowNotes',[])->contain(['Evaluations']));` in Controller method and `= h($academicTeacher->evaluation->final_note) ?>` in a template, but didn't work. I got notice about trying to retrieve property of non-object... Any suggestions? – Milven Jan 29 '17 at 16:41

1 Answers1

0

This is not right.

return $q->where(['Evaluations.final_note' < 3]);

It should be.

return $q->where(['Evaluations.final_note <' => 3]);
styks
  • 3,193
  • 1
  • 23
  • 36
  • Ok, now the problem is: `public function findLowNotes(Query $query, array $options) { return $this->find() ->distinct(['AcademicTeachers.id']) ->matching('Evaluations', function ($q) { return $q->where(['Evaluations.final_note <' => 3]); }); }` ...doesn't return nothing at all, so it was bad idea already. Now i think, that maybe it's beacause i have extra join table AcademicTeachersEvaluation for the teachers who are responsible for evaluation. Is it possible that it mess original idea? ShallI move it to other place? For example, evaluation model? – Milven Jan 27 '17 at 15:19