How To Make SQL Statements Without Yii's Interference

Yii 2.0 General Discussions
1

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] =&gt; 42S02


[1] =&gt; 1146


[2] =&gt; 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!

2

yii\db\Connection is a relatively thin wrapper around PHP’s PDO. It doesn’t parse or alter the statements. The error you got is a PDO exception.

3

Okay thanks for that but in Yii1 and also when I execute the query directly using the database tools it works perfectly. So is there anything else that I’ m missing? Maybe the way I’m connecting to the database in Yii?

4

Okay my bad!! I should have realized that I had changed my connection to point to another database. All is well now. Nothing to see here! :blink:

5

That was very sneaky of you :)