Design pattern to handle queries using multiple models

Question

I am presented with a dilemma while trying to re-designing the class structure for my PHP/MySQL application to make it more elegant and conform it to the SOLID principle.

The problem goes like this:

Let as assume, there is an abstract class called person which has certain properties to define a generic person, such as name, age, date of birth etc.

There are two classes, student, and teacher, that implements this abstract class. They add their own unique properties to it.

I have designed all the three classes to include all the operational logic (details of which are not relevant in context of the question).

Now, I need to create views/reports/data grids which contain details from multiple classes, for example, say, a list of all students doing projects in Chemistry mentored by a teacher whose name is the parameter to the query.

This is just one example of a view, there are many different views in the application, which uses data from 3-4 tables, and each of them have multiple input parameters to generate them.

Considering this particular example, I have written the relevant query using JOIN and the results are as expected and proper, now here is the dilemma:

Keeping in mind the single responsibility principle, where should I keep this query? It does not belong to either Student class, or Teacher class or any other classes currently present.

a) Should I create a new class, say dataView class, and design it as a MVC pattern and keep the query there? What about the other views? how do they fit in this architecture?

b) Should I not keep the query in code at all, and make it DB View ?

c) Am I completely wrong in the approach? If so what is the right approach?

My considerations are as follows:

a) should be easy to add new views later on if requirement comes, without having to copy-paste-modify code

b) would like to make it as loosely coupled as possible so that if minor db structure changes happen, it does not break

I did google searches on report design and OOP report generators, but all the result seem to focus on the visual design of the report rather than fetching the data. I have already taken care of the visual aspect of the report using MVC with html templates.

I am sure this is a very fundamental problem with known solution, but I am somehow not able to find it (maybe searching with wrong keyword).

Edit1: Modified the title to make it more relevant

Edit2: The accepted answer got me thinking in the right direction and identify my design flaws, which eventually led me to find this question and the solution in Stack Overflow which gave me the detailed answer to clear the confusion.

Answer 1

Your title does not seem to mach your question, and your question has little to do with reporting, so that might explain why you couldn't find much.

Your problem start to emerge here:

I have designed all the three classes to include all the operational logic (details of which are not relevant in context of the question).

I don't think you should put "operational logic" (do you mean data access operations like get, update and delete?) in your model classes.

for example, say, a list of all students doing projects in Chemistry mentored by a teacher whose name is the parameter to the query.

To me, it sounds like you're looking for the repository pattern. Something like:

class StudentRepository
{
    private $_database;

    function GetStudentByID($id)
    {
        return $_database..[query with $id];
    }

    function GetStudentsForSubjectAndTeacher($subjectID, $teacherID)
    {
        return $_database..[query with $subjectID, $teacherID];
    }
}

Now everywhere you want to find all students for a given subject and teacher, you just create an instance of this repository and call the appropriate method.

Answer 2

Well, if i understand the query, your dilemma is where to put this query and not whether the query is to be put in a model class considering you are following MVC pattern.

My take: If you see the query, you are ultimately fetching students. Fetching students can be based on projects, male, female, class, age, teachers etc.

I feel, the model should be student_model.php which has various methods, where in your query would go

eg: fetchMaleStudents(), fetchFemaleStudents(), fetchStudentsForProjects(projectName), fetchStudentsForTeacher(teachersName) and so on.