Hello:
I’m trying to figure out how to make a fairly complicated SQL query in Yii2. In Yii1.x I was able to use the:
$conn=Yii::app()->db;
$result = $conn->createCommand($query)->queryAll(true);
but in Yii2 it appears that the ActiveRecord code is parsing the SQL and determining that it does not understand it instead of just allowing me to execute it against the underlying database.
Here’s an example of what the raw SQL looks like:
SELECT pts.pts_id, pts.begin_class, pts.end_class, pts.cancel_class, pts.no_class, pts.begin_time, pts.end_time, pts.status, pts.begin_grade, pts.class_details, pts.weekdays, WEEKDAY(pts.begin_class)+1 wkday, IF(pts.weekdays != '',pts.weekdays,WEEKDAY(pts.begin_class)+1) daysofweek, pts.end_grade, p.name, s.name school, s.type, s.phone, s.address1, s.city, s.zip, group_concat(distinct concat(sub.first_name, ' ', sub.last_name,
case c.change_type when 1 THEN ' on ' else ' Help/Train ' end
, date_format(c.sub_date, '%m/%d')) separator ',') subs, group_concat(distinct date_format(c2.sub_date, '(%m/%d) '), time_format(c2.start_time,'Class Time: %l:%i %p - '), time_format(c2.end_time,'%l:%i %p '), c2.notes separator ', ') changes FROM program_to_school pts LEFT JOIN school s ON (pts.school_id=s.school_id) LEFT JOIN program p ON (pts.program_id=p.program_id) LEFT JOIN changes c on (pts.pts_id = c.pts_id AND c.change_type in (1,3) AND c.sub_date BETWEEN '2014' AND '2014') LEFT JOIN changes c2 on (pts.pts_id = c2.pts_id AND c2.change_type = 2 AND c2.sub_date BETWEEN '2014' AND '2014') LEFT JOIN instructor sub on (c.instructor_id = sub.instructor_id) WHERE (pts.status IN ('1','2') AND pts.school_id in (select school_id from school where franchise_id = 1) AND (begin_class <= '2014' AND end_class >= '2014') AND (WEEKDAY(begin_class) = WEEKDAY('2014') OR pts.weekdays LIKE '%3%')) GROUP BY pts.pts_id ORDER BY WEEKDAY(begin_class), begin_time, begin_class ASC
and my code in Yii2 (in part):
$connection = \Yii::$app->db;
$command = $connection->createCommand($query);
$results = $command->queryAll();
The types of errors that I’m seeing are:
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘crm.program_to_school’ doesn’t exist
and
Error Info: Array
(
[0] => 42S02
[1] => 1146
[2] => Table 'crm.program_to_school' doesn't exist
)
↵
Caused by: PDOException
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘crm.program_to_school’ doesn’t exist
in /Applications/MAMP/htdocs/pa/vendor/yiisoft/yii2/db/Command.php at line 837
Executing the query directly against the database (outside of Yii) works perfectly fine. Is there a way to do this in Yii2?
Thanks!