Options->where( function )

Options->where( function )

carrarachristophecarrarachristophe Posts: 106Questions: 25Answers: 2

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?

Answers

  • allanallan Posts: 63,439Questions: 1Answers: 10,459 Site admin

    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

  • carrarachristophecarrarachristophe Posts: 106Questions: 25Answers: 2

    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":[]}]}
    
  • allanallan Posts: 63,439Questions: 1Answers: 10,459 Site admin

    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

  • carrarachristophecarrarachristophe Posts: 106Questions: 25Answers: 2

    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

  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421
    edited June 2

    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 );
            } )
        )
    
  • carrarachristophecarrarachristophe Posts: 106Questions: 25Answers: 2

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

  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421

    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.

  • allanallan Posts: 63,439Questions: 1Answers: 10,459 Site admin
    edited June 3

    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

Sign In or Register to comment.