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

wundii

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

allan

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

Allan

wundii

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' ),

allan

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

wundii

sorry for the bad description

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

allan

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

wundii

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

allan

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

wundii

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,

allan

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