I’d like to get some guidance on where it makes sense to put a query that does not fit in any standard ActiveQuery framework. This is a query that is formatting the data as it is retrieving it from the database and so even though MOST Of the data is just columns from a table the rest of it is concatenated fields with custom column names.
How would you handle this? Create a separate Query class? Or should I just add a method to the ActiveRecord class that I know won’t return a straight result of ActiveRecords but returns an array of specially formatted records?
Just trying to figure out what the best design pattern in Yii2 is for something like this.
If you have a db query that does not fit well with the tools in YII2, you can just create a function in your model that performs the raw SQL and any data processing you need.
For example here is one of my functions that I could not fit into Query Builder in the time I had available.
In this example I am having my SQL server do some of my formatting work for me such as name and date formatting.
In this function I am not using bound parameters because the calling function gets the current userid from the system.
Otherwise always use bound parameters to prevent SQL-Injections attacks.
public static function GetStudents( $userid )
{
$connection = \Yii::$app->db;
$sql = "
SELECT
CONCAT(Students.firstname, ' ', Students.lastname) AS student_name,
Sessions.name AS session_name, Students.studentId, Sessions.sessionId,
CONCAT(DATE_FORMAT(Sessions.startdate, '%a %b %d'), ' --> ', DATE_FORMAT(Sessions.enddate, '%a %b %d') ) AS session_date
FROM
Students
LEFT JOIN
Registrations ON Students.studentId = Registrations.studentId
LEFT JOIN
Sessions ON Sessions.sessionId = Registrations.sessionId
WHERE
Students.parentsId = " . $userid . "
ORDER BY lastname , firstname , startdate;";
$command = $connection->createCommand( $sql );
$students = $command->queryAll(); // returns results as an array
return $students;
}
I think it’s better. The model is meant to be a record. There are, of course, shortcut methods such as find() but these are very simple: either giving you model right away or returning a Query. The getStudents() method does not work with AR models and doesn’t depend on any AR model. I’d create StudentRepository class for it.
