Options->where( function )

carrarachristophe

Hello,
I am trying to implement the Options->where( function ) as described for exemple here.

Here is my code:

        Field::inst( 'quote.quote_datetime' )
            ->options( Options::inst()
                ->where( function ($q) { $q->where( 'quote.quote_datetime', '( SELECT MAX(quote.quote_datetime) FROM quote left Join patrimoine_instruments ON quote.quote_isin = patrimoine_instruments.instrument_ISIN WHERE patrimoine_instruments.instrument_ISIN = quote.quote_ISIN LIMIT 1;)', '=', false); })),

which returns "DataTables warning: table id=patrimoines - 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 MySQL server version for the right syntax to use near 'FROM WHERE (quote.quote_datetime = ( SELECT MAX(quote.quote_datetime) FROM' at line 1"

When I replace quote.quote_ISIN by a value that is in the datatable,like:

        Field::inst( 'quote.quote_datetime' )
            ->options( Options::inst()
                ->where( function ($q) { $q->where( 'quote.quote_datetime', '( SELECT MAX(quote.quote_datetime) FROM quote left Join patrimoine_instruments ON quote.quote_isin = patrimoine_instruments.instrument_ISIN WHERE patrimoine_instruments.instrument_ISIN = 'FR0000050353' LIMIT 1;)', '=', false); })),

it returns DataTables warning: table id=patrimoines - Ajax error. For more information about this error, please see https://datatables.net/tn/7 code 500.

I know that the SELECT query below is correct because runing this query returns a correct value:
SELECT MAX(quote.quote_datetime) FROM quote left Join patrimoine_instruments ON quote.quote_isin = patrimoine_instruments.instrument_ISIN WHERE patrimoine_instruments.instrument_ISIN = 'FR0000050353' LIMIT 1;
so I guess the problem comes from the rest of the code.

Any idea on where it could come from?

allan

Could you add ->debug(true) just before the ->process(...) call please? Then reload your page and show me the full JSON response from the server. Hopefully that will let me track down what is going on.

Allan

carrarachristophe

Hi Allan,
Is it what you were expecting?

{"fieldErrors":[],"error":"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 MySQL server version for the right syntax to use near 'FROM   WHERE (`quote`.`quote_datetime` = ( SELECT MAX(quote.quote_datetime) FROM' at line 1","data":[],"ipOpts":[],"cancelled":[],"debug":["Editor PHP libraries - version 2.3.2",{"query":"SELECT  `patrimoines`.`patrimoine_id` as 'patrimoines.patrimoine_id', `patrimoines`.`patrimoine` as 'patrimoines.patrimoine', `patrimoines`.`instrument` as 'patrimoines.instrument', `patrimoine_instruments`.`instrument_ISIN` as 'patrimoine_instruments.instrument_ISIN', `patrimoine_instruments`.`instrument` as 'patrimoine_instruments.instrument', `patrimoine_instruments`.`instrument_symbole` as 'patrimoine_instruments.instrument_symbole', `patrimoine_instruments`.`instrument_marche` as 'patrimoine_instruments.instrument_marche', `patrimoine_instruments`.`instrument_devise` as 'patrimoine_instruments.instrument_devise', `patrimoine_instruments`.`instrument_emetteur` as 'patrimoine_instruments.instrument_emetteur', `patrimoine_instruments`.`instrument_adresse` as 'patrimoine_instruments.instrument_adresse', `patrimoine_sousclasses_actifs`.`sousclasse` as 'patrimoine_sousclasses_actifs.sousclasse', `patrimoine_classes_actifs`.`classe` as 'patrimoine_classes_actifs.classe', `patrimoines`.`utilisateur` as 'patrimoines.utilisateur', `utilisateurs`.`utilisateur` as 'utilisateurs.utilisateur', `patrimoines`.`banque` as 'patrimoines.banque', `banques`.`code` as 'banques.code', `banques`.`nom` as 'banques.nom', `patrimoines`.`support_type` as 'patrimoines.support_type', `patrimoine_support_types`.`code` as 'patrimoine_support_types.code', `patrimoine_support_types`.`type` as 'patrimoine_support_types.type', `patrimoine_support_types`.`categorie` as 'patrimoine_support_types.categorie', `patrimoine_categories`.`code` as 'patrimoine_categories.code', `patrimoine_categories`.`categorie` as 'patrimoine_categories.categorie', `patrimoines`.`parts` as 'patrimoines.parts', `patrimoines`.`valeur` as 'patrimoines.valeur', `quote`.`quote_ISIN` as 'quote.quote_ISIN', `quote`.`quote_datetime` as 'quote.quote_datetime', `quote`.`quote_last` as 'quote.quote_last' FROM  `patrimoines` LEFT JOIN `utilisateurs` ON `utilisateurs`.`utilisateur_id` = `patrimoines`.`utilisateur`  LEFT JOIN `banques` ON `banques`.`banque_id` = `patrimoines`.`banque`  LEFT JOIN `patrimoine_instruments` ON `patrimoine_instruments`.`instrument_id` = `patrimoines`.`instrument`  LEFT JOIN `patrimoine_sousclasses_actifs` ON `patrimoine_sousclasses_actifs`.`sousclasse_id` = `patrimoine_instruments`.`instrument_sousclasse`  LEFT JOIN `patrimoine_classes_actifs` ON `patrimoine_classes_actifs`.`classe_id` = `patrimoine_sousclasses_actifs`.`classe`  LEFT JOIN `patrimoine_support_types` ON `patrimoine_support_types`.`type_id` = `patrimoines`.`support_type`  LEFT JOIN `patrimoine_categories` ON `patrimoine_categories`.`categorie_id` = `patrimoine_support_types`.`categorie`  LEFT JOIN `quote` ON `quote`.`quote_isin` = `patrimoine_instruments`.`instrument_ISIN` WHERE `patrimoines`.`utilisateur` = :where_0 ","bindings":[{"name":":where_0","value":1,"type":null}]},{"query":"SELECT DISTINCT  `instrument_id` as 'instrument_id', `instrument` as 'instrument' FROM  `patrimoine_instruments` ","bindings":[]},{"query":"SELECT DISTINCT  `banque_id` as 'banque_id', `nom` as 'nom' FROM  `banques` ","bindings":[]},{"query":"SELECT DISTINCT  `type_id` as 'type_id', `type` as 'type' FROM  `patrimoine_support_types` ","bindings":[]},{"query":"SELECT DISTINCT  `categorie_id` as 'categorie_id', `categorie` as 'categorie' FROM  `patrimoine_categories` ","bindings":[]},{"query":"SELECT DISTINCT   FROM   WHERE (`quote`.`quote_datetime` = ( SELECT MAX(quote.quote_datetime) FROM quote left Join patrimoine_instruments ON quote.quote_isin = patrimoine_instruments.instrument_ISIN WHERE patrimoine_instruments.instrument_ISIN = quote.quote_ISIN LIMIT 1;) )","bindings":[]}]}

allan

This is the query that is failing:

SELECT DISTINCT
FROM
WHERE (
    `quote`.`quote_datetime` = (
        SELECT MAX(quote.quote_datetime)
        FROM quote
        left Join patrimoine_instruments ON quote.quote_isin = patrimoine_instruments.instrument_ISIN
        WHERE patrimoine_instruments.instrument_ISIN = quote.quote_ISIN
        LIMIT 1;
    )
)

There is no select selected, or a table to select it from. I think you need to add calls to the table(), value() and label() methods of the Options class. See the documentation and examples here.

Allan

carrarachristophe

Hi Allan,
I did have a look to the documentation and came to the following:

        Field::inst( 'quote.quote_datetime' )
            ->options( Options::inst()
                ->table( 'quote' )
                ->value( 'quote' )
                ->label( 'quote.quote_datetime' )
                ->where( function ($q) {
                        $q->where( 'quote.quote_datetime', '( SELECT MAX(quote.quote_datetime) FROM quote left Join patrimoine_instruments ON quote.quote_isin = patrimoine_instruments.instrument_ISIN WHERE patrimoine_instruments.instrument_ISIN = FR0000050353 LIMIT 1;)', 'LIKE'); 
                })

It leads to no more error message but the outcome is not what I expect:
1. First,
* instead of having 1 row for each patrimoine_instruments.instrument_ISIN (for the max value of quote.quote_datetime),
* the Datatable displays 1 row for each value in table quote for a specific patrimoine_instruments.instrument_ISIN
2. Then, maybe due to the first problem, the value returned in the field "quote.quote_datetime" (last column below) is not the max one but the one for each of the date on which there is a value in the table "quote"

Example below: instead of having a single row with 2024-05-31 17:35:00 returned in the last column, 4 rows are displayed because there are 4 values for patrimoine_instruments.instrument_ISIN=FR0000050353 in table quote

rf1234

I think you have a problem with the quotes. Not using a quote for the hard coded ISIN value of FR0000050353 should not lead to the right result. Using a single quote within an SQL-statement that itself is in single quotes shouldn't work either. I would also bind the value. That eliminates the quote issue anyway.

This could work:

Field::inst( 'quote.quote_datetime' )
    ->options( Options::inst()
        >table( 'quote' )
        ->value( 'quote' )
        ->label( 'quote.quote_datetime' )
        ->where( function ($q) { 
            $q->where( 'quote.quote_datetime', 
                       '( SELECT MAX(a.quote_datetime)
                            FROM quote a
                       LEFT JOIN patrimoine_instruments b ON a.quote_isin = b.instrument_ISIN 
                           WHERE b.instrument_ISIN = :ISIN 
                           LIMIT 1 )', '=', false );
            $q->bind( ':ISIN', 'FR0000050353' );               
        } )
    )

Without binding the ISIN you could do this using double quotes for the hard-coded ISIN string.

Field::inst( 'quote.quote_datetime' )
    ->options( Options::inst()
        >table( 'quote' )
        ->value( 'quote' )
        ->label( 'quote.quote_datetime' )
        ->where( function ($q) { 
            $q->where( 'quote.quote_datetime', 
                       '( SELECT MAX(a.quote_datetime)
                            FROM quote a
                       LEFT JOIN patrimoine_instruments b ON a.quote_isin = b.instrument_ISIN 
                           WHERE b.instrument_ISIN = "FR0000050353" 
                           LIMIT 1 )', '=', false );
        } )
    )

carrarachristophe

Hello rf1234,
Thank you for your proposal.
None of the solutions worked.
I am still facing the exact same 2 problems.

rf1234

Ok, then please post your database structure, e.g. a screenshot from mysql workbench. I got the exact same queries working many times. I assume there's something wrong with your database model.

allan

I'm a little confused here - the Options query will only have an impact on the list of options for a field. But you've shown a DataTable in the screenshot, not a select. It might be a datatable field? But I don't know as I can't see your full code.

It would be helpful if you:

1. Linked to a test case showing the issue
2. Show your full PHP for this controller.

Thanks,
Allan