Server Side Editor::inst a field with 2 MySQL if`s not work

Server Side Editor::inst a field with 2 MySQL if`s not work

wundiiwundii Posts: 5Questions: 0Answers: 0

Hello i have little problem,

i use one if on a field and this works, but when use two if in one field then it does not work

my code, this does not work:
Editor::inst( $db, 'wc_ap as wap')
->fields(
Field::inst( 'wap.id' )->name ( 'id' ),
Field::inst( 'if(ifnull(sm.Aktiv,2) = 0,3,if(ifnull(wap.status,'')>'',2,wap.aktiv))' )->name ( 'aktivstatus' )
)
->leftJoin( 'system_ma as sma', 'sma.id', '=', 'wap.a_id' )
->leftJoin( 'system_m as sm', 'sm.id', '=', 'sma.m_id' )
->process( $_POST )
->json();

with this line works:
Field::inst( 'if(ifnull(sm.Aktiv,2) = 0,3,wap.aktiv)' )->name ( 'aktivstatus' )

give a better way for my problem or i`m blind?

andi :)

Replies

  • allanallan Posts: 54,743Questions: 1Answers: 8,565 Site admin

    Are you saying that if you add Field::inst( 'wap.id' )->name ( 'id' ) it fails? What error message does it give you?

    Allan

  • wundiiwundii Posts: 5Questions: 0Answers: 0

    This is my error message.

    An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'as '3'

    Field::inst( 'if(ifnull(sm.Aktiv,2) = 0,3,if(ifnull(wap.status,0)>0,2,wap.aktiv))' )->name ( 'aktivstatus' )

    with one if it´s all okay, but by two and more not.
    this works: Field::inst( 'if(ifnull(sm.Aktiv,2) = 0,3,wap.aktiv)' )->name ( 'aktivstatus' ),

  • allanallan Posts: 54,743Questions: 1Answers: 8,565 Site admin

    Sorry, I'm still not clear on this. Do you mean that:

    ->fields(
      Field::inst( 'if(ifnull(sm.Aktiv,2) = 0,3,if(ifnull(wap.status,'')>'',2,wap.aktiv))' )->name ( 'aktivstatus' )
    )
    

    works, but:


    ->fields( Field::inst( 'wap.id' )->name ( 'id' ), Field::inst( 'if(ifnull(sm.Aktiv,2) = 0,3,if(ifnull(wap.status,'')>'',2,wap.aktiv))' )->name ( 'aktivstatus' ) )

    doesn't?

    Allan

  • wundiiwundii Posts: 5Questions: 0Answers: 0
    edited July 2018

    sorry for the bad description :neutral:

    when i use in the Field::inst two if( ... , ... , if( ... , ... , ... )), then doesn't works the script, but when i use one if conditions, then work it's...

    this works:
    Field::inst( 'if(ifnull(sm.Aktiv,2) = 0,3,wap.aktiv)' )->name ( 'aktivstatus' )

    doesn't works:
    Field::inst( 'if(ifnull(sm.Aktiv,2) = 0,3,if(ifnull(wap.status,0)>0,2,wap.aktiv))' )->name ( 'aktivstatus' )

  • allanallan Posts: 54,743Questions: 1Answers: 8,565 Site admin

    Ah I see. Try adding ->debug(true) before the -process() call. That will output the SQL that Editor is executing as part of the JSON. Can you show me what that JSON is please?

    I suspect that you might actually be better moving that logic to the client-side, but let's see what the SQL is.

    Allan

  • wundiiwundii Posts: 5Questions: 0Answers: 0
    edited July 2018

    Hello, i tried this and i have found following syntax error in the mysql line
    IF( ifnull( sm.Aktiv, 2 ) = 0, '3' AS '3', IF( ifnull( wap.STATUS, 0 ) > 0, 2, wap.aktiv ) ),
    '3' AS '3'

    I see that correctly this is a bug in the Field::inst what two and more conditions with ( ... ) not processed properly, because he set the AS ... in the first ).

    Andi

  • allanallan Posts: 54,743Questions: 1Answers: 8,565 Site admin

    Sorry, I've not quite understand. Do you mean that if there are two sets of parenthesis in the field string, the bug happens?

    Thanks,
    Allan

  • wundiiwundii Posts: 5Questions: 0Answers: 0
    edited July 2018

    if you have two and more if conditions, then he sets that AS not correctly.

    the mysql result from
    Field::inst( 'if(ifnull(sm.Aktiv,2) = 0,3,if(ifnull(wap.status,0)>0,2,wap.aktiv))' )->name ( 'aktivstatus' )

    datatabels make:
    MySQL IF( ifnull( sm.Aktiv, 2 ) = 0, '3' AS '3', IF( ifnull( wap.STATUS, 0 ) > 0, 2, wap.aktiv ) ),
    but this is not correct... AS '3' is too early... the AS conditions must be at the end from this line.

    correct is:
    MySQL IF( ifnull( sm.Aktiv, 2 ) = 0, 3, IF( ifnull( wap.STATUS, 0 ) > 0, 2, wap.aktiv ) ) AS Fieldname,

  • allanallan Posts: 54,743Questions: 1Answers: 8,565 Site admin

    Sorry for the delay in getting back to you. This is a limitation in how Editor is currently trying to parse the statement. It tries to split a string into individual fields on the comma. It checks for a single level of parenthesis, but not nested.

    Thinking about it, I'm going to remove that comma parsing. The string passed in should be treated as a single statement. If multiple fields are needed, an array or multiple arguments can be used.

    In Database/Query.php you will find:

                    // String argument so split into pieces and add
                    $fields = preg_split( "/,(?![^()]*+\\))/", $args[$i] );
    
                    for ( $j=0 ; $j<count($fields) ; $j++ ) {
                        $this->_field[] = trim( $fields[$j] );
                    }
    

    Change that to be simply:

                    $this->_field[] = trim( $get );
    

    That will resolve the issue. I'm going to include this in the 1.8 release of Editor.

    Regards,
    Allan

This discussion has been closed.