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


(Dev) #1

Hello:

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.

Thanks!


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

			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;

    }




(Alexander Makarov) #4

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


(John) #5

Samdark,

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

Thanks

-John


(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.