How to perform mysql functions in Yii2 query?

Question

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?

Is anything going wrong (errors showing or being logged)?

Script47
– Script47

2017-09-07 08:22:14 +00:00
Commented Sep 7, 2017 at 8:22 — Script47
– Script47, Commented Sep 7, 2017 at 8:22
I've updated my question with the error

HELPME
– HELPME

2017-09-07 08:28:07 +00:00
Commented Sep 7, 2017 at 8:28 — HELPME
– HELPME, Commented Sep 7, 2017 at 8:28
@HELPME I have updated my answer. Try that.

ajmedway
– ajmedway

2017-09-07 09:50:01 +00:00
Commented Sep 7, 2017 at 9:50 — ajmedway
– ajmedway, Commented Sep 7, 2017 at 9:50

ajmedway · Accepted Answer · 2017-09-07 10:47:58Z

2

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');

edited Sep 7, 2017 at 10:47

answered Sep 7, 2017 at 8:39

ajmedway

1,49214 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ajmedway Over a year ago

I have just taken a longer look at this. I cannot advise precisely on the solution without information on your database schema. Are the updated_at and term_months fields in the client_service table?

karpy47 · Accepted Answer · 2017-09-07 09:46:46Z

1

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');

answered Sep 7, 2017 at 9:46

karpy47

9006 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to perform mysql functions in Yii2 query?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related