[FIXED] How to perform update operations on columns of type JSONB in Postgres 9.4

Issue

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns.

Documentation for JSONB types and functions:

http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html

As an examples, I have this basic table structure:

CREATE TABLE test(id serial, data jsonb);

Inserting is easy, as in:

INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

Now, how would I update the 'data' column? This is invalid syntax:

UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1;

Is this documented somewhere obvious that I missed? Thanks.

Solution

Ideally, you don't use JSON documents for structured, regular data that you want to manipulate inside a relational database. Use a normalized relational design instead.

JSON is primarily intended to store whole documents that do not need to be manipulated inside the RDBMS. Related:

• JSONB with indexing vs. hstore

Updating a row in Postgres always writes a new version of the whole row. That's the basic principle of Postgres' MVCC model. From a performance perspective, it hardly matters whether you change a single piece of data inside a JSON object or all of it: a new version of the row has to be written.

Thus the advice in the manual:

JSON data is subject to the same concurrency-control considerations as any other data type when stored in a table. Although storing large documents is practicable, keep in mind that any update acquires a row-level lock on the whole row. Consider limiting JSON documents to a manageable size in order to decrease lock contention among updating transactions. Ideally, JSON documents should each represent an atomic datum that business rules dictate cannot reasonably be further subdivided into smaller datums that could be modified independently.

The gist of it: to modify anything inside a JSON object, you have to assign a modified object to the column. Postgres supplies limited means to build and manipulate json data in addition to its storage capabilities. The arsenal of tools has grown substantially with every new release since version 9.2. But the principal remains: You always have to assign a complete modified object to the column and Postgres always writes a new row version for any update.

Some techniques how to work with the tools of Postgres 9.3 or later:

• How do I modify fields inside the new PostgreSQL JSON datatype?

This answer has attracted about as many downvotes as all my other answers on SO together. People don't seem to like the idea: a normalized design is superior for regular data. This excellent blog post by Craig Ringer explains in more detail:

• "PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns"

Another blog post by Laurenz Albe, another official Postgres contributor like Craig and myself:

• JSON in PostgreSQL: how to use it right

Answered By - Erwin Brandstetter

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

What are CRUD Operations?

CRUD is a quick way of describing the basic functions of a database.

If you're new to web development, or any programming for that matter, you'll come across the term CRUD. It's a concept that is often mentioned throughout coding bootcamps and online resources like this one. But what is it?

CRUD stands for Create, Read, Update and Delete. These are the four basic functions of persistent storage. When it comes to databases, the concepts used to describe these operations are insert, select, update and delete.

These four operations often form the basis of most web application architectures. The full acronym is commonly referred to as CRUD: Create, Read (or retrieve), Update and Delete.

 

Create

Create is the process of adding new data to a table. When creating new data, it’s important to ensure that the necessary fields are completed and filled with accurate information (or, if it is intentionally left blank, that this is something you need to do). The data should also be complete: all of the fields required to create the data should be filled in.

Read

Read operations are the most commonly used operation in a database. Read operations allow you to retrieve data from a database and is performed through a select statement. Data that is returned can be based on certain criteria defined in the select statement, for example, where you only want to return books written by J.K Rowling.

Update

Update:

The update operation changes the information stored in a database. With this operation, you can change the value of a single field or multiple fields.

In update operations, data validation is very important to ensure that your database doesn't accept any invalid data.

Delete

The final CRUD operation is delete. This one is pretty straightforward: delete removes a record from the database entirely. As you can imagine, this might have some unintended consequences—if your app allows users to create records and then delete them, they may make a mistake when deleting that they can't take back. To avoid this kind of thing, it's common practice to mark records as

Read More

[FIXED] when I submit my form it doesn't save the information in the database, I use laravel 8

Issue

when I submit my form it doesn't save the information in the database, instead it just refreshes the page, if I delete the relation with my request in the controller the record is saved in the

Read More

[FIXED] delete button with confirmation in yii2

Issue

This view.php is generated with CRUD in Yii2 but the delete button doesn't confirm window.

But in index.php generated with CRUD in Yii2, The confirm window for delete worked.

<p>
    <?= Html::a('Edit', ['update', 'id' => $model->id], ['class' => 'btn btn-primary']) ?>
    <?= Html::a('Delete', ['delete', 'id' => $model->id], [
            'class' => 'btn btn-danger',
            'data' => [
                'confirm' => 'Are you sure you want to delete this item?',
                'method' => 'post',
            ],
    ]) ?>
    <?= Html::a('Back','index', ['class' => 'btn btn-warning']) ?>
</p>

Delete Button in index.php:

<a href="var/delete?id=2" title="Delete" aria-label="Delete" data-pjax="0" data-confirm="Are you sure you want to delete this item?" data-method="post"><span class="glyphicon glyphicon-trash"></span></a>

Delete Button in view.php:

<a class="btn btn-danger" href="var/delete?id=2" data-confirm="Are you sure you want to delete this item?" data-method="post">Delete</a>

Solution

The section about yii.js is still in progress.

To get default support of confirmation you should register yii.js.

Using yii\web\YiiAsset

use yii\web\YiiAsset;
YiiAsset::register($this);

or registering script files (not recommend, just for example)

$this->registerJsFile('@yii/assets/yii.js', ['depends' => [\yii\web\JqueryAsset::className()]]);

Answered By - SiZE

Read More

[FIXED] Re-arrange a CakePHP JSON response on object prior to render

Issue

I am working on an CakePHP REST/CRUD based API. It uses $routes->setExtensions(['json']); within /config/routes.php to make the it's responses into json objects.

I am working with several objects that have complex schema's that I need to pre-process prior to to submitting to the CakeORM, in order to simplify the API integration for the end user.

For instance the following is the json blob that would be needed to be patched to the ORM using $this->ImportSettings->patchEntity($importSetting, $requestData[2]):

    {
        "id": 2,
        "generic_setting": "Hello World",
        "import_source_google_setting": null,
        "import_source_csv_ordered_setting": null,
        "import_source_csv_headed_setting": {
            "id": 1,
            "import_settings_id": 2,
            "delimiterId": 1 
        },
        "import_destination_user_setting": null,
        "import_destination_asset_setting": {
            "id": 2,
            "import_settings_id": 2,
            "defaultValueId": 1 
        }
    }

There can be one of many sources and one of many designation settings defined on an import setting. To simplify this for the API user I am allowing them to submit, the following:

    {
        "id": 2,
        "generic_setting": "Hello World",
        "import_source_setting": {
            "id": 1,
            "import_settings_id": 2,
            "delimiterId": 1 
        },
        "import_destination_setting": {
            "id": 2,
            "import_settings_id": 2,
            "defaultValueId": 1 
        }
    }

I have written code into an event listener on beforeMarshal for ImportSesttings that is able to tell if the index "import_source_setting" belongs in in the tables "import_source_csv_headed_setting", "import_source_csv_ordered_setting" or "import_source_google_setting" and likewise with asset and user settings going into "import_destination_setting".

This works well for processing a re-organizing data in a request before it enters the ORM. However I would like to do the same thing now with the data before it is displayed, so the API user does not need to look at the addtional source and destination settings.

I have accomplished this through the use of middleware in a similar use case in another part of the system. However the middleware seems to be made to attach to routes, my uses seems more like something that should be tied to the model life cycle so it runs whenever an import settings is returned and properly modifies the output, even when nested.

Given what I am looking for, what part of Cake should I place this logic that re-organizes the json response on the ORM query result for a table in? Can your point me to documentation on this?

Solution

I came across an answer for this in another forum using CakePHP's calculated fields. It looks like the formatResults() function can be attached to a query with a callback to re-organize the results after the query is ran. I went ahead and attached it to the query in the beforeFind() event, which seems to work.

See example below:

<?php

class ImportSettingsListener implements Cake\Event\EventListenerInterface
{
    public function implementedEvents(): array
    {
        return [
            'Model.beforeFind' => 'generateQuery',
        ];
    }

    public function generateQuery(Event $event, Query $query, ArrayObject $options, bool $primary): void
    {
        $query->formatResults(function (CollectionInterface $results) {
            return $results->map(function ($setting) {
                // Re-format $setting here
                return $setting;
            });
        });
    }
}

Answered By - space97

Read More

[FIXED] Show validation errors in crud api

Issue

How can I display the validations error and messages in the returned response. I just retrieve response like this:

{
  "message": "4 validation errors occurred",
  "url": "/api/posts",
  "code": 422
} 

Solution

I found it here:

The Exception handler need a configuration:

-If your CakePHP >= 3.3 (middleware feature):

'Error' => [
    'errorLevel' => E_ALL,
    'exceptionRenderer' => 'Crud\Error\JsonApiExceptionRenderer',
    'skipLog' => [],
    'log' => true,
    'trace' => true,
],

-If your CakePHP < 3.3 :

<?php
class AppController extends Controller {

  public function initialize()
  {
    parent::initialize();
    $this->Crud->config(['listeners.api.exceptionRenderer' => 'App\Error\ExceptionRenderer']);
  }
}

Answered By - Eymen Elkum

Read More

[FIXED] EasyAdmin choiceField bug Symfony

Issue

I think there's a bug in EasyAdmin 3.5.9, but before creating an issue in GitHub, I would like to have people's opinion.

In my User CRUD, I wanted to be able to edit the user role, which wasn't displying by default. So I added it like so in UserCrudController class:

public function configureFields(string $pageName): iterable
{
    $roles = [
        'User'   => 'ROLE_USER',
        'Agency' => 'ROLE_AGENCY',
        'Admin'  => 'ROLE_ADMIN'
    ];

    return [
        // ...
        ChoiceField::new('roles')
            ->setChoices($roles)->allowMultipleChoices(),
        // ...
    ];
}

Here is how it looks in User entity:

/**
 * @see UserInterface
 */
public function getRoles(): array
{
    $roles = $this->roles;

    $roles[] = 'ROLE_USER';
    $roles[] = 'ROLE_AGENCY';
    $roles[] = 'ROLE_ADMIN';

    return array_unique($roles);
}

public function setRoles(array $roles): self
{
    $this->roles = array_values($roles);

    return $this;
}

Actually, when I want to edit a user with EasyAdmin by selecting one of these choices, it save the right value in database. But when I come back on the same user to edit it, still all choices are displaying in the field, like I did nothing before. So it seems to be more a displaying issue than a data saving issue.

Here is a quick screen record to understand better: https://watch.screencastify.com/v/If9xScwQAFqcbbfGBmza

Hope you can help me.

Solution

Your UserInterface::getRoles() is manually adding all the roles back in with the set roles. The method should only need the following:

public function getRoles(): array
{
    return array_unique($this->roles);
}

Answered By - Arleigh Hix

Read More

[FIXED] Force JSON format using CakePHP 3 and Crud plugin

Issue

I have followed How to build a CakePHP 3 REST API tutorial to create REST API. Tutorial describes case when requests are made with extensions (json/xml) to set response format. Plugin CRUD for CakePHP is used.

I want to force JSON response format without using .json extension. I am getting MissingRouteException on my requests.

What have I tried

Router::prefix('api', function ($routes) {
    //$routes->extensions(['json', 'xml']);
    $routes->resources('Cocktails');
});

plus

#1

$this->RequestHandler->ext = 'json' into AppController::beforeFilter()

#2

$this->RequestHandler->renderAs($this, 'json'); into AppController::beforeFilter()

but this is trying to use template from Template/Api/Coctails/json

I want it to behave exactly like in the case with extension.

Thanks

Solution

Request your data with a proper HTTP accept header Accept: application/json, the RequestHandler should pick it up then.

The Accept header is used by HTTP clients to tell the server what content types they'll accept. The server will then send back a response, which will include a Content-Type header telling the client what the content type of the returned content actually is.

However, as you may have noticed, HTTP requests can also contain Content-Type headers. Why? Well, think about POST or PUT requests. With those request types, the client is actually sending a bunch of data to the server as part of the request, and the Content-Type header tells the server what the data actually is (and thus determines how the server will parse it).

In particular, for a typical POST request resulting from an HTML form submission, the Content-Type of the request will normally be either application/x-www-form-urlencoded or multipart/form-data.

Answered By - floriank

Read More

[FIXED] RESTful / Crud based approach to controlling an entity behavior that is typically implicit

Issue

I am modifying a CakePHP application to have an API available on it. My intention is to try to keep keep the endpoints as close to being RESTful / Crud oriented as possible. Although I have a use case that that I am unsure of.

I have the following requests for adding and editing tasks:

PUT       /tasks
PATCH     /tasks/:id

One of the behaviors of task entity in the system I am working on is that they send emails to the effected users associated with the task, when a save or edit is performed. This allows the various parties surrounding the task to be updated on the status of the particular task.

However the the one issue is that in some uncommon cases the end user will need to be able to toggle if they want an email to be sent on the front end.

What is the proper RESTful / Crud oriented approach to flag the task endpoints to not fire the email in the API request?

There is no record of the email in the application's database and it is nice to have to functionality tied into the task life cycle hooks and called implicitly. So I am not crazy about doing something like creating an /emailTask set of endpoints. It seems like an optional flag in the task request would be cleaner, but might not be maintainable if we begin to have similar needs for other behaviors associated with tasks.

Thanks in advance for the help!

Solution

PUT /tasks

If you're intending to use this for adding tasks, use POST instead. PUT /tasks implies that you are overwriting all tasks.

As for side-effects, this to me feels like a decent use-case for a custom HTTP header. Perhaps something like Suppress-Notifications: ?1 ?

Why ?1 as a value? This is going to be the future default for new HTTP headers that specify a boolean:

https://datatracker.ietf.org/doc/html/draft-ietf-httpbis-header-structure-15#section-4.1.9

Answered By - Evert

Read More