CODEWITHSUNDEEP
phpmysqlyii2
HELPME
HELPME

Reputation: 754

How to perform mysql functions in Yii2 query?

I'm always getting syntax errors when I'm trying to perform mysql functions in my Yii2 query.

What I need to do is select a third column updated_at and do the mysql function, which is written in the variable

Here's the code:

        $contractExpiryDate = 'DATE_ADD(FROM_UNIXTIME(updated_at), '
        . 'INTERVAL term_months MONTH) AS contract_expiry_date';

    $query = self::find()
        ->select([
            self::tableName() . '.*',
            Client::tableName() . '.company_name AS company_name',
            ClientService::tableName() . '.agreement_no AS agreement_no',
            ClientService::tableName() . $contractExpiryDate,
        ])->joinWith('client')
          ->joinWith('loans')
          ->orderBy('company_name');

Error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS contract_expiry_date FROM client_profit_loss_report LEFT JOIN client ON' at line 1 The SQL being executed was: SELECT client_profit_loss_report.*, client.company_name AS company_name, client_service.agreement_no AS agreement_no, client_serviceDATE_ADD(FROM_UNIXTIME(updated_at), INTERVAL term_months MONTH) AS contract_expiry_date FROM client_profit_loss_report LEFT JOIN client ON client_profit_loss_report.client_id = client.id LEFT JOIN client_profit_loss_report_loan ON client_profit_loss_report.id = client_profit_loss_report_loan.report_id LEFT JOIN client_service ON client_profit_loss_report_loan.client_service_id = client_service.id ORDER BY company_name

Could someone explain what I'm doing wrong?

Upvotes: 0

Views: 1354

Answers (2)

ajmedway
ajmedway

Reputation: 1492

This should be the code that works for you. You need to reference the table name on each field:

$query = self::find()
    ->select([
        self::tableName() . '.*',
        Client::tableName() . '.company_name AS company_name',
        ClientService::tableName() . '.agreement_no AS agreement_no',
        'DATE_ADD(
            FROM_UNIXTIME(' . ClientService::tableName() . '.updated_at),
            INTERVAL ' . ClientService::tableName() . '.term_months MONTH
        ) AS contract_expiry_date',
    ])->joinWith('client')
    ->joinWith('loans')
    ->orderBy('company_name');

If you wish to format the date as per you other comment, you simply need to wrap DATE_ADD function call in a DATE_FORMAT function call, like so:

DATE_FORMAT(DATE_ADD('2017-09-07', INTERVAL 1 MONTH), '%Y-%m-%d');

So with your query, like this:

$query = self::find()
    ->select([
        self::tableName() . '.*',
        Client::tableName() . '.company_name AS company_name',
        ClientService::tableName() . '.agreement_no AS agreement_no',
        'DATE_FORMAT(
            DATE_ADD(
                FROM_UNIXTIME(' . ClientService::tableName() . '.updated_at),
                INTERVAL ' . ClientService::tableName() . '.term_months MONTH
            ),
            '%Y-%m-%d'
        ) AS contract_expiry_date',
    ])->joinWith('client')
    ->joinWith('loans')
    ->orderBy('company_name');

Upvotes: 2

karpy47
karpy47

Reputation: 900

Not sure about to which table each column goes, but try this...

$contractExpiryDate = 'DATE_ADD(FROM_UNIXTIME('. ClientService::tableName() .'.updated_at), '
    . 'INTERVAL '. ClientService::tableName() .'.term_months MONTH) AS contract_expiry_date';

$query = self::find()
    ->select([
        self::tableName() . '.*',
        Client::tableName() . '.company_name AS company_name',
        ClientService::tableName() . '.agreement_no AS agreement_no',
        $contractExpiryDate,
    ])->joinWith('client')
      ->joinWith('loans')
      ->orderBy('company_name');

Upvotes: 1

Related Questions