Set default column value using ApiTools

Hi!

I am trying to send a POST Request to my service with a “date” field whitch is no nullable and it has column default value “CURRENT_TIMESTAMP”. But I can not get success.
It returns this: “(23000 - 1048 - Column ‘date’ cannot be null)”

I can see, POST Request send all fields empty/NULL or not. So Laminas\Db\TableGateway\AbstractTableGateway try to insert “date” field with NULL value. Can I choice which fields use for each CRUD request?

What can I do?
Thank you so much.

What kind of logic are you using for the form? Im not familiar with the API tools so Im not sure if it uses Laminas form or not? If you’re not using Laminas form can you not unset the post key for date (just to test it) and if that solves it then look for a better approach? If its not present at all, then the RDBMS will auto insert the correct value based on the default.

If you are using Laminas/Form then set a validation group and exclude the date form field, which should omit it from the Post array for the incoming data. Which means it will not be present, so it can not be null.

Hello Tyrsson!

I’m just using API Tools UI.

As I can see debugging with Postman, DbConnectedResource trying to insert all fields in database.

Now, I will try to re-implement DbConnectedResource::create as a solution.

retrieveData function adds the other fields.

Sorry, I am not familiar with the API tools at that level. However, I do I have a question. If the table is going to set the default. Why are you adding it as a field at all? It will be time stamped at the time the record is created without naming the field there will it not? Maybe im overlooking something that has to do with best practice in the “API” world in regards to all fields needing to be present and if so I apologize but please let me know if so, that way I can learn something :slight_smile: I know there is most likely 10 RFC’s that list what should and shouldnt be done in regards to that.

What does DbConnectedResource::create look like? In ::create can you not just unset that array key before its passed to the tablegateway? I ask because I am not familiar enough with the API tools to know if you do or not, its literally been 8+ years since I did any testing using the API tools.

The guy to ask about this would most likely be @ezkimo

I had to track down his username so it took me a minute.

Hey guys,

thanks for marking me here. My time is currently very limited due to a client project. Nevertheless, I would like to discuss two solutions with you here. The first one is simply extending your resource class. I guess your issue targets the create method of the \Laminas\ApiTools\DbConnectedResource class. Nevertheless you can easily extend any resource method you need to.

<?php
declare(strict_types=1);
namespace Marcel\V1\Rest\MyResource;

use DateTimeImmutable;
use Laminas\ApiTools\DbConnectedResource;

class MyResource extends DbConnectedResource
{
    public function create($data)
    {
        if (!isset($data['date_created']) || $data['date_created'] === null) {
            $data['date_created'] = (new DateTimeImmutable())->format('Y-m-d H:i:s');
        }

        return parent::create($data);
    }
}

If you 're extending from \Laminas\ApiTools\Rest\Resource you could even implement an event listener, because the rest resource triggers an event for create actions. An event listener would be coded one time and can be executed on every create event. The event listener can manipulate the data, too. That could be the second possible solution, which depends on how you 've implemented your service.

Another thing on your SQL table design. If your column is a DATETIME type your default CURRENT_TIMESTAMP can result in an issue depending on the mysql version. In case you 're using MySQL. Beware of update statements and CURRENT_TIMESTAMP when the table column was created with date_created DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

The described behaviour of your rest service sounds pretty normal. Your date_created field is not required but is in the collection of fields for the input filter. If not given the field is retrieved as null from the input filter. The following logic tries to send explicitly a NULL instead of a current timestamp or datetime value but your table column does not permit NULL values. Your database says no and results into an error. You could unset the date_created key after the input filter was executed and test, if that works.

Dear friends,
I really really appreciate your collaboration! Sorry for my late reply.
I was be able to fix it changing default column value and customizing resource file.

Thank you!