Multiple IF in Field::inst

jaeweb

Hi

I'm regularly using IF in my Field::inst Server-Script. But sometimes the SQL is constructed incorrectly (checking output in debugger). Then, after the "as" the fielname is missing and the IF statement is repeated as field name in ''. There is no SQL-Error.

Here is the example:

Field::inst('
    IF(
        tablename.Abgelehnt = 1,
        "Abgelehnt",
        IF(
            tablename.KeineReaktion = 1,
            "Keine Reaktion",
            IF(
                CURDATE() < DATE_FORMAT(tablename.Anfang, "%Y-%m-%d"),
                "Offen",
                IF(
                    tablename.Anfang IS NULL AND tablename.Ende IS NULL,
                    "Offen",
                    IF(
                        NOT tablename.Anfang IS NULL AND NOT tablename.Ende IS NULL AND tablename.Unterschrieben <> 1,
                        "Offen",
                        IF(
                            CURDATE() > DATE_FORMAT(tablename.Ende, "%Y-%m-%d"),
                            "Abgeschlossen",
                            "Laufend")
                    )
                )
            )
        )
    )
', 'Status' )

for better readability i indented everything but actually it looks like this:

Field::inst( 'IF(tablename.Abgelehnt=1,"Abgelehnt",IF(tablename.KeineReaktion=1,"Keine Reaktion",IF(CURDATE()<DATE_FORMAT(tablename.Anfang,"%Y-%m-%d"),"Offen",IF(tablename.Anfang IS NULL AND tablename.Ende IS NULL,"Offen",IF(NOT tablename.Anfang IS NULL AND NOT tablename.Ende IS NULL AND tablename.Unterschrieben<>1,"Offen",IF(CURDATE()>DATE_FORMAT(tablename.Ende,"%Y-%m-%d"),"Abgeschlossen","Laufend"))))))', 'Status' )

Output from debugger:

IF
    (
        tablename.Abgelehnt = 1,
        "Abgelehnt",
        IF(
            tablename.KeineReaktion = 1,
            "Keine Reaktion",
            IF(
                CURDATE() < DATE_FORMAT(tablename.Anfang, "%Y-%m-%d"),
                "Offen",
                IF(
                    tablename.Anfang IS NULL AND tablename.Ende IS NULL,
                    "Offen",
                    IF(
                        NOT tablename.Anfang IS NULL AND NOT tablename.Ende IS NULL AND tablename.Unterschrieben <> 1,
                        "Offen",
                        IF(
                            CURDATE() > DATE_FORMAT(tablename.Ende, "%Y-%m-%d"),
                            "Abgeschlossen",
                            "Laufend")
                        )
                    )
                ))
            ) AS 'IF(tablename.Abgelehnt=1,"Abgelehnt",IF(tablename.KeineReaktion=1,"Keine Reaktion",IF(CURDATE()<DATE_FORMAT(tablename.Anfang,"%Y-%m-%d"),"Offen",IF(tablename.Anfang IS NULL AND tablename.Ende IS NULL,"Offen",IF(NOT tablename.Anfang IS NULL AND NOT tablename.Ende IS NULL AND tablename.Unterschrieben<>1,"Offen",IF(CURDATE()>DATE_FORMAT(tablename.Ende,"%Y-%m-%d"),"Abgeschlossen","Laufend"))))))'

I have already seen this: https://datatables.net/forums/discussion/51104/server-side-editor-inst-a-field-with-2-mysql-if-s-not-work

But I'm using version is 1.10.19

Any ideas? Just too complex? I thought of using table rendering but then the programmed data filters don't work anymore. It would be nice to have a "SQL only" solution.

If I remove some of the IF functions the value is returned correctly. For example this works:

Field::inst( 
    IF(
        tablename.Abgelehnt = 1,
        "Abgelehnt",
        IF(
            tablename.KeineReaktion = 1,
            "Keine Reaktion",
            IF(
                CURDATE() < DATE_FORMAT(tablename.Anfang, "%Y-%m-%d"),
                "Offen",
                IF(
                    CURDATE() > DATE_FORMAT(tablename.Ende, "%Y-%m-%d"),
                    "Abgeschlossen",
                    "Laufend")
                )
            )
        ) 
    )
', 'Status' )

Thanks in advance

allan

I rather suspect you are running into an SQL identifier limit - if it is working with smaller condition sets, then you add more, that effects our as aliasing output. It looks like 256 is the limit.

To change that we would need to rework our libraries to have a unique short alias rather than using the full string. That isn't going to be a trivial task, but is none-the-less something that we have on our list.

Allan