PostgreSQL and Datatables

menashe

I reorganized my PostgreSQL database. I am (finally) able to once again connect--my DataTables and Editor forms show the data from the DB, but nothing in the DB is being modified.

For example, when using F12 Developer Tools, the Payload tab shows the form change:

But the Preview tab (what is sent to the DB) shows no change:

allan

What are you using for your server-side database writes?

Allan

menashe

Editor PHP.

allan

Can you show me that code please?

menashe

Sorry for the slow turnaround; I chaperoned a school trip today.

Please note that the addition of the prefix shopping schema name before table names is--to the best of my knowledge--the only change since I reorganized the DB.

<?php

// DataTables PHP library
include("../../Editor-PHP-2.1.3/lib/DataTables.php");

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst($db, 'shopping.items')
  ->fields(
        Field::inst('items.id')->set(false),                   
      Field::inst('items.item')
          ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'An Item name is required'))),
            Field::inst('items.prefix')
                    ->setFormatter( Format::ifEmpty( null ) ),
            Field::inst('items.suffix')
                    ->setFormatter( Format::ifEmpty( null ) ),
      Field::inst('items.category_id')
          ->options(Options::inst()
                  ->table('shopping.categories')
                  ->value('id')
                  ->label('category')
          )
          ->validator( Validate::dbValues(null, 'id', 'shopping.categories', $db))
          ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'A Category is required'))),
      Field::inst('categories.category'),
      Field::inst('categories.sub_category1'),
      Field::inst('categories.sub_category2'),
            Field::inst('items.manufacturer_id')
                  ->options(Options::inst()
                          ->table('shopping.manufacturers')
                          ->value('id')
                          ->label('manufacturer')
                  )
                    ->setFormatter( Format::ifEmpty( null ) ),
      Field::inst('manufacturers.manufacturer'),
      Field::inst('items.brand_id')
          ->options(Options::inst()
                  ->table('shopping.brands')
                  ->value('id')
                  ->label('brand')
          )
                    ->setFormatter( Format::ifEmpty( null ) ),
      Field::inst('brands.brand'),
      Field::inst('items.notes')
                    ->setFormatter( Format::ifEmpty( null ) ),
    )
  ->leftJoin('shopping.brands', 'brands.id', '=', 'items.brand_id')
  ->leftJoin('shopping.categories', 'categories.id', '=', 'items.category_id')
  ->leftJoin('shopping.manufacturers', 'manufacturers.id', '=', 'items.manufacturer_id')
  ->process($_POST)
  ->debug(true)
  ->json();

menashe

Good morning Allan,

ADDENDUM:

I solved it, but do not understand why that works!

I renamed my "shopping" schema (in PostgreSQL) to 'public'--as it was originally--and I deleted ALL of the "shopping." prefixes in ALL of my PHP files.

My app now shows data AND I can edit/modify.

WHY??

If I want to have different named schemas within a database, what do I need to do??

allan

It sounds like it is related to the search path that Postgres is using. If you put the ->debug(true) call before ->process($_POST) (after is too late to record the information), then show me the JSON response from the broken state, that might give me a clue (assuming you want to dig into this more, or if you want to use your workaround for now, that's fine).

Allan

menashe

Summary: I reorganized yet again--two databases--for unrelated project, each one with a public schema. I am not sure that Editor PHP can handle a differently-named schema.
(At work, where we used Oracle and .NET, we have one database with many schemas, each for a different project.)

allan

It certainly should. I use Postgres schemas extensively for CloudTables.

Allan

menashe

Allan,

I was just able to load and modify DataTable data from the "shopping" schema in the "shopping" database!

But ONLY by setting SET search_path TO shopping in Postgres.

That can't be right! But nothing I do in the PHP config files (config.php and database.php) as well as the .env file makes it read from the shopping schema.

How do I do this from within the DataTables app?

menashe

I found this from January:

$db->sql('SET search_path TO schema');

But where do I put that?

menashe

Nevermind--right before the Editor... in the PHP.

Brilliant!

You seem to answer so many questions, definitely can't fault you for not remembering!

allan

That will do it - nice one. Thanks for the updates and great to hear you got it working!

Allan