What Is Proper Place for SQL Queries That Don't Fit the Normal ActiveQuery Framework

(Dev) #1


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.


(Tedjoujospin) #2

I’m facing a similar issue. Let’s wait answers !

(John) #3

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 = "


				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




				Registrations ON Students.studentId = Registrations.studentId


				Sessions ON Sessions.sessionId = Registrations.sessionId


				Students.parentsId = " . $userid . "

			ORDER BY lastname , firstname , startdate;";

		$command = $connection->createCommand( $sql );

		$students = $command->queryAll(); // returns results as an array

		return $students;


(Alexander Makarov) #4

I’d create a seaprate class that’s not inherited from anything.

(John) #5


Is this better or just different from writing another function in the model like I did above? And how?



(Alexander Makarov) #6

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.