error with SQL function as Editor field - Editor PHP Lib 1.7.3

Balubaer

Hello everybody,

I'm not sure, if this is really a bug or a fault on my side but when I try to use a MySQL function for a field in the editor (of the php editor lib, v1.7.3) the generated SQL message (picked from the debug mode) has a fault.

My code (snippet)

    // ...
    $editor = Editor::inst($db, "projectsoverview");

    $editor->debug(true);
    
    $editor->fields(
        Field::inst("DATEDIFF(delivery__dateselect, NOW())", "days_remaining")
            ->set(false),
        Field::inst("id"),
        Field::inst("project__id")
            ->setFormatter("Format::nullEmpty"),
        // much more fields 
    );
    // ...

Using this definition (DATEDIFF function) I always receive the error An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1583 Incorrect parameters in the call to native function 'datediff'. Trying to inspect this by using

    $editor->debug(true);

I receive the following content (snippet) in the debug node of the JSON answer:

SELECT
    `id` as 'id',
    DATEDIFF(delivery__dateselect as 'DATEDIFF(delivery__dateselect', NOW()) as 'NOW())',
    `project__id` as 'project__id',
    [... much more fields ...]

As you can see the construction of the SQL select seems to have an error. Even trying to use

Field::inst("DATEDIFF(delivery__dateselect, NOW()) as days_remaining", "days_remaining")

or

Field::inst("DATEDIFF(delivery__dateselect, NOW()) as days_remaining")

results in a mis-constructed SQL statement.

Or do I misunderstand the usage of SQL functions as (non-writeable) columns for the data?

Greetings,
Dennis

allan

Hi Dennis,

You are absolutely correct. I'm afraid there is a bug in 1.7.3 that is causing this error. If you look in php/Database/Query.php you'll find the line:

if ( $addAlias && $field !== '*' ) {

replace it with:

if ( $addAlias && $field !== '*' && strpos($field, '(') === false ) {

And it will start working!

Regards,
Allan

Balubaer

Hi Allan,

thanks for you fast reply.

So far this works, but unfortunately not with an extended (and originally intended) SQL function call like this

Field::inst("IFNULL(DATEDIFF(delivery__dateselect, NOW(), 99999999) as days_remaining", "days_remaining")

because you're using the original statement for the ORDER BY part of the SQL statement no matter if I use the second parameter of the inst function call and/or the "as" extension.

Short explanation: this I use, because I want to (optionally) order this column having the NULL values at the bottom when ordering ascending (and having the most negative values at top).

It would also be cool to have the NULL valued rows also at the end when ordering descending, but this is just nice to have (maybe you also have a solution for this case?).

Maybe you have a hint or a solution for this?

Thanks,
Dennis

allan

Yes, if you are using server-side processing, that is going to fail.

What you need to do, if you can't disable server-side processing (i.e. you had tens of thousands or more rows).

The way to address this is to include the unformatted date as a field, and also the formatted one as another field. Use the columns.data option to point at the unformatted field, and columns.render to display the formatted one.

A bit of a workaround, but it will work.

Allan

Balubaer

Hi Allan,

what do you mean with formatted and unformatted date fields? And how might it help to have an unformatted field to achieve my custom ordering?

Unfortunately I'm not able to disable server-side processing because of the huge amount of data I have to handle.

And besides all that isn't it possible to change the construction of the SQL statement to use the (optionally) set column name (by "as" suffix or by the second parameter in the Field::inst call) for the ORDER BY/GROUP BY part? (To be honest I'm not that fit in SQL and all it's derivatives used in the different RDBMSs).

Kind regards,
Dennis

allan

Let me get back to you about this - I've another question about it as well which points out that my solution above doesn't allow for filtering on the server-side. With server-side processing its a bit more tricky - I'll need to have a bit of an experiment with the libraries as they were explicitly designed for that I'm afraid (at least, not yet ).

Allan

Balubaer

Hi Allan,

okay, so I'll wait for an solution (or maybe find the time to take a look by myself and support you).

If you have any progress on it please be so kind and let me know.

Thanks so far,
Dennis

Balubaer

Hi Allan,

I don't want to hurry you in any way but I'm wondering, if you already got the time to get deeper into your used libraries?

I think the problem can be solved a quite easy way by not ignoring the "as" parameter/string-part when using a function as column in a SQL statement.

A valid Example might be like:

SELECT  `id` as 'id', IFNULL(DATEDIFF(delivery__dateselect, NOW()), 9999999) as days_remaining, `delivery__dateselect` as 'delivery__dateselect' FROM  `projectsoverview` ORDER BY days_remaining asc LIMIT 117 OFFSET 0

The problem I see right now is that your query builder will ignore the "as" part (whether by 2nd parameter of Field::inst() or as 'as' string) so the SQL looks like

SELECT  `id` as 'id', IFNULL(DATEDIFF(delivery__dateselect, NOW()), 9999999), `delivery__dateselect` as 'delivery__dateselect' FROM  `projectsoverview` ORDER BY IFNULL(DATEDIFF(delivery__dateselect, NOW()), 9999999) asc LIMIT 117 OFFSET 0

I guess the fault ORDER BY part is created because the column name information got lost somewhere.

I tried to fix it by myself in the _build_field function of Database\Query.php but even there the field list ($this->_field) is already broken.

Output of var_dump($this->_field):

array (size=40)
  0 => string 'id' (length=2)
  1 => string 'IFNULL(DATEDIFF(delivery__dateselect' (length=36)
  2 => string 'NOW()), 999999)' (length=15)
  3 => string 'project__id' (length=11)
...

Maybe you have a (quick) solution for this or any further hints how to handle this? Thanks.

Kind Regards,
Dennis

allan

Hi Dennis,

Apologies for the delay in getting back to you on this one! As you say, the libraries will ignore the as statement if it looks like a function (they basically don't touch the statement, since all sorts of things such as sub-queries with as statements could be used). A full SQL parser, or at least something a lot more complicated than is currently used would really be needed.

At the moment, the only workaround, if you want to retain server-side sorting for that column, is to use a VIEW.

What I'm looking into is some way to explicitly provide an SQL alias and have the libraries use that. Its not trivial though unfortunately, so likely it will be 1.8 before we see that.

Allan

Balubaer

Hi Allan,

the hint with the view sounds good. I'll try it that way.

Thanks for your idea - and your replay.

I'll post a comment here, if it was successfull (if others may face a similar problem).

Greetings,
Dennis

allan

I've just made a change in this thread that will have an impact here. The original statement should work with Editor 1.8+ (when it is released!).

Allan