Options->where( function )
Options->where( function )
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
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
Hi Allan,
Is it what you were expecting?
This is the query that is failing:
There is no select selected, or a table to select it from. I think you need to add calls to the
table()
,value()
andlabel()
methods of theOptions
class. See the documentation and examples here.Allan
Hi Allan,
I did have a look to the documentation and came to the following:
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
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:
Without binding the ISIN you could do this using double quotes for the hard-coded ISIN string.
Hello rf1234,
Thank you for your proposal.
None of the solutions worked.
I am still facing the exact same 2 problems.
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.
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 aselect
. It might be adatatable
field? But I don't know as I can't see your full code.It would be helpful if you:
Thanks,
Allan