PostgreSQL and Datatables

PostgreSQL and Datatables

menashemenashe Posts: 150Questions: 35Answers: 1

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:

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • menashemenashe Posts: 150Questions: 35Answers: 1

    Editor PHP.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you show me that code please?

  • menashemenashe Posts: 150Questions: 35Answers: 1

    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();
    
  • menashemenashe Posts: 150Questions: 35Answers: 1

    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??

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • menashemenashe Posts: 150Questions: 35Answers: 1

    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.)

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • menashemenashe Posts: 150Questions: 35Answers: 1

    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?

  • menashemenashe Posts: 150Questions: 35Answers: 1

    I found this from January:

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

    But where do I put that?

  • menashemenashe Posts: 150Questions: 35Answers: 1

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

    Brilliant!

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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

Sign In or Register to comment.