Retrieving information from DB ro utilize render function in Selectize

menashe

I feel that I should know this, but, somehow, I do not know how to proceed.

I am trying to accomplish something akin to the "type: datatable" in the Editor. In that situation, the back-end jQuery code populates all of the fields that I specify in my Editor.

I am using the Selectize add-in, and I want to retrieve information so that I can nicely render the row(s) and have multi-column comboboxes/pulldowns.

My Editor PHP:

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

if ( ! isset($_POST['packaging_id']) || ! is_numeric($_POST['packaging_id']) ) {
    echo json_encode( [ "data" => [] ] );
}
else {
    Editor::inst($db, 'prices')
    ->field(
        Field::inst('prices.id')->set(false),
        Field::inst('prices.packaging_id'),
        Field::inst('prices.store_id')
            ->options( Options::inst()
                    ->table('stores')
                    ->value('id')
                    ->label('store')
            )
          ->validator( Validate::notEmpty( ValidateOptions::inst()
              ->message( 'A Store name is required')
              )
                ),
        Field::inst('stores.store'),
        Field::inst('stores.address'),
        Field::inst('stores.city'),
        Field::inst('stores.state'),
        Field::inst('prices.date')
            ->getFormatter( Format::dateSqlToFormat( "Y-m-d" ) )
            ->setFormatter( Format::dateFormatToSql( "Y-m-d" ) ),
                Field::inst('prices.price')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('prices.is_sale')
                    ->getFormatter( function ( $val, $data ) {
                                return $val == 1
                                    ? 'Yes'
                                    : 'No';
                    } )
                    ->setFormatter( function ( $val, $data ) {
                                return $val == 'Yes'
                                    ? 1
                                    : null;
                    } ),
        Field::inst('prices.sale_start')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('prices.sale_end')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('prices.sale_price')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('prices.sale_units')
                        ->setFormatter( Format::ifEmpty( 1 ) ),
        Field::inst('prices.dollars_off')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('prices.percent_off')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('prices.is_taxable')
                    ->getFormatter( function ( $val, $data ) {
                                return $val == 1
                                    ? 'Yes'
                                    : 'No';
                    } )
                    ->setFormatter( function ( $val, $data ) {
                                return $val == 'Yes'
                                    ? 1
                                    : null;
                    } ),
        Field::inst('prices.taxrate_id')
            ->options( function($db) {
                return $db->select('taxrates', array('id', 'tax_rate', 'tax_city', 'tax_state'))->fetchAll();
            } )
                        ->setFormatter( Format::ifEmpty( null ) )
            ->validator( Validate::dbValues(null, 'id', 'taxrates') ),
        Field::inst('prices.notes')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('taxrates.tax_rate'),
        Field::inst('taxrates.tax_city'),
        Field::inst('taxrates.tax_state'),
        Field::inst('prices.unit_price')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('packaging.unit_id')
            ->options( Options::inst()
                    ->table('units')
                    ->value('id')
                    ->label('unit')
            ),
        Field::inst('units.unit'),
        Field::inst('units.description'),
        )
    ->leftJoin('packaging', 'packaging.id', '=', 'prices.packaging_id')
    ->leftJoin('stores', 'stores.id', '=', 'prices.store_id')
    ->leftJoin('taxrates', 'taxrates.id', '=', 'prices.taxrate_id')
    ->leftJoin('units', 'units.id', '=', 'packaging.unit_id')
    ->where( 'prices.packaging_id', $_POST['packaging_id'] )
    ->debug(true)
    ->process($_POST)
      ->json();
  }

This returns the following:

In the render, the only information available to me is the label and value. So what I get is:

But you'll notice that the information that I want is available in the bottom part of the above-screenshot, under "option"--stores, city, state, etc.

How do I retrieve that information to display in the Selectize?

Thanks!!

menashe

Correction, of course:

What I expanded, under "options" is just the label and value.

But above, under "data," it shows the Store, City, State for the current store.

How do I populate that information for all of the stores?

menashe

Hi,
I was wondering if you have any information to share regarding my previous question.
When using Editor PHP, we use the 'option' to return one field from a related table (foreign key) to display in a 'type: select' or 'type: datatable'.
How can I return two or more fields to display in a 'select' (or 'selectize')?

allan

Hi,

Sorry for the delay in replying here! I totally missed your post. The Options class provides the option of specifying an array of labels which could then be combined with the render option - e.g. from the example on that page:

Field::inst( 'users.site' )
    ->options( Options::inst()
        ->table( 'sites' )
        ->value( 'id' )
        ->label( array('name', 'country') )
        ->render( function ( $row ) {
            return $row['name'].' ('.$row['country'].')';
        } )
    );

In your case you would expand the ->label('store') to include all the fields you want and use a renderer to create the string needed. I'm not sure how multiple column layout would be possible in Selectize though - that would be one for the Selectize support folks.

Allan

menashe

Allan,
Thank you! I said to myself--the post probably fell through the cracks!

I found a Selectize example, so that should work.
I had also previously seen the example you provided--but it NEVER occurred to me:

Do I understand correctly that what you saying is to render a string in Editor PHP and then decompose that string for the Selectize render??

Wow!

allan

If you need to change the string that is returned by the server before display in Selectize, then yes, you could decompose and reformat the string. However, it might be easiest to just have the render function at the server return the string format you need to display as the label in Selectize.

Allan