Convert SQL query to yii2?

Question

Until now I have been using a SQL query in yii2 which was working fine locally but as soon as I deploy it to the server it shows

I have tried changing it to yii query since its a proper implementation below

$query = $connection->createCommand("SELECT a.name_of_flight, a.time_of_flight, (a.no_of_passenger - b.cnt) as avail, a.no_of_passenger FROM flight_schedule a LEFT JOIN (SELECT flight_time, COUNT(id) AS cnt FROM book_eticket WHERE flight_date='$date' AND company_name = '$comp_name' GROUP BY flight_time) b ON a.id = b.flight_time")->queryAll();

to

$query = (new \yii\db\Query());

$query
    ->select('a.name_of_flight, a.time_of_flight, (a.no_of_passenger - b.cnt) as avail, a.no_of_passenger')
    ->from('flight_schedule a')
    ->leftJoin('flight_time', ('COUNT(id) AS cnt FROM book_eticket'))
    ->where(array('and', 'flight_date=2016-6-29', 'company_name = Team5'))
    ->groupBy(['flight_time b','ON a.id = b.flight_time']);

$command = $query->createCommand();
$query = $command->queryAll();

but I get an error:

Can anybody help me find out the problem? Thanks in advance

about first: you probably doing something wrong. Can you provide you db config? — Maksym Semenykhin
$query = $connection->createCommand("SELECT a.name_of_flight, a.time_of_flight, (a.no_of_passenger - b.cnt) as avail, a.no_of_passenger FROM flight_schedule a LEFT JOIN (SELECT flight_time, COUNT(id) AS cnt FROM book_eticket WHERE flight_date='$date' AND company_name = '$comp_name' GROUP BY flight_time) b ON a.id = b.flight_time")->queryAll(); — Suyog Rajbhandari

Rinat Rinat · Accepted Answer · 2016-06-29T08:40:21

First screen says about access issues. Second - that operands like date and team is not string. That is wrong. Can you quote them?

Convert SQL query to yii2?

1 Answers