editor PHP mysql function and join

Maikel

Hey, i have the following editor code

Editor::inst( $db, 'bedrijf', 'id' )
        ->fields(
            Field::inst( 'id' ),
            Field::inst( 'naam' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Naam moet ingevuld worden' )
            )),
            Field::inst( 'crv_login' ),
            Field::inst( 'crv_pass' ),
            Field::inst( 'crv_type' ),
            Field::inst( 'unifarm_id' ),
            Field::inst( 'celgetal_1' ),
            Field::inst( 'celgetal_2' ),
            Field::inst( 'actief' ),
            Field::inst( 'getLastDataDate(id)', 'lastDataPoint')->set(False),
        )
        ->process( $_POST )
        ->debug(True)
        ->json();

I now want to add a lefjoin, the lefjoin is pretty simple, but i still want the function to be called.

if i try to combine this with the left join i get the following error:

error 'Table part of the field "getLastDataDate(id)" was not found. In Editor instances that use a join, all fields must have the database table set explicitly.'

    Editor::inst( $db, 'bedrijf', 'id' )
            ->fields(
                Field::inst( 'bedrijf.id' ),
                Field::inst( 'bedrijf.naam' )
                ->validator( Validate::notEmpty( ValidateOptions::inst()
                    ->message( 'Naam moet ingevuld worden' )
                )),
                Field::inst( 'bedrijf.crv_login' ),
                Field::inst( 'bedrijf.crv_pass' ),
                Field::inst( 'bedrijf.crv_type' ),
                Field::inst( 'bedrijf.unifarm_id' ),
                Field::inst( 'bedrijf.celgetal_1' ),
                Field::inst( 'bedrijf.celgetal_2' ),
                Field::inst( 'bedrijf.actief' ),
                Field::inst( 'getLastDataDate(id)', 'lastDataPoint')->set(False),
            )
            ->leftJoin('report', 'report.bedrijf_id', '=','bedrijf.id')
            ->process( $_POST )
            ->debug(True)
            ->json();

allan

I've got to confess, I didn't think of this scenario! You could probably just comment out this part in your local copy, and, although I haven't tried it, I expect that it should work.

Allan

rf1234

There is also another way to do this like this. That shouldn't cause any problems with "leftJoin". I am using this all the time.

// Field::inst( 'getLastDataDate(id)', 'lastDataPoint')->set( false ),
Field::inst('bedrijf.id AS lastDataPoint')->set( false )
    ->getFormatter( function($val, $data, $opts) {
       return getLastDataDate($val);
    }),     

Maikel

in this last example is getLastDataDate en sql function?

rf1234

Don't know Maikel.

But you could easily pass the db handler to the function like this

Field::inst('bedrijf.id AS lastDataPoint')->set( false )
    ->getFormatter( function($val, $data, $opts) use ($db){
       return getLastDataDate($val, $db);
    }),    

Here is an example from my own coding using Editor's db handler and Editor's "raw" method:

Field::inst( 'ctr_govdept.id AS ctr_govdept.has_contracts' )->set( false )
    ->getFormatter( function($val, $data, $opts) use ($db){
        $result = $db->raw()
            ->bind( ':fk', $val )  
            ->exec( 'SELECT COUNT(*) AS ctrCount 
                       FROM ctr_has_ctr_govdept  
                      WHERE ctr_govdept_id = :fk' );
        $row = $result->fetch(PDO::FETCH_ASSOC);
        if ( (bool)$row["ctrCount"] ) {
            return 1;
        }
        return 0;
    }),