Left Join - left field - delay between date

ocin35

hi,
$RAW_SQL_QUERY="SELECT ID, Ref, DtCreat, Left(Code,2) AS Depart, Ville, Objet, Domaine, Presta_Nom, Statut, DtRealis, DtDemandClos, DtClos, NumPanier, MontPanier, Comment FROM tbl_Demand, tbl_presta WHERE lien_tblpresta = Presta_ID";

Editor::inst( $db, $RAW_SQL_QUERY, 'ID' )
question #1: how to run editor :: inst with a sql request please?

in my dataTable I would like a field calculating the delay between the current date and a date field of my table
question # 2: how to do it please?

I would like to display only the first 2 digits of the "code" field in ajax (sql = Left (Code, 2) as Depart)
Question # 3: How can I display this in one of my columns from my dataTables please?

Thank you for your answers

colin

Hi @ocin35 ,

I suspect it would be more efficient to do both of those as part of the rendering (using columns.render in the browser, rather than on the server side.

For the date, use Moment.js, as it's excellent for all time/date based operations.

For the first two letters, something like this would do the trick,

Cheers,

Colin

ocin35

Thank you so much colin
Cheers,
ocin35

ocin35

hi,
for questions #2 and #3: solution found

can anyone help me for question #1 please using a simple example?
Thank you

allan

question #1: how to run editor :: inst with a sql request please?

You can't I'm afraid. The closest you can get is to create a VIEW with your statement and then use Editor to build its SELECT against that.

Regards,
Allan

ocin35

Hi,
solution found!
the following code loads the fields from the "TblSuivi" table and retrieves the "Presta_Name" field from the linked table "TblPresta" - link between table: TblSuivi.LienPresta -> TblPresta.ID

if it helps someone
thank you

Editor::inst( $db, 'TblSuivi', 'ID' )
->fields(
Field::inst( 'Ref' ),
Field::inst( 'DtCreat' ),
Field::inst( 'Site' ),
Field::inst( 'Objet' ),
Field::inst( 'LienPresta' ), (mandatory otherwise error!!)
Field::inst( 'DtRealis' )
->validator( Validate::dateFormat( 'Y-m-d' ) )
->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
Field::inst( 'Price' )
->validator( Validate::numeric() )
->setFormatter( Format::ifEmpty(null) )
)
->join(
Mjoin::inst( 'TblPresta' )
->link( 'TblSuivi.LienPresta', 'TblPresta.ID' )
->fields(
Field::inst( 'Presta_Name' )
)
)
->where( 'Resp', $Resp, $Cond ) (example -> where('Resp', 'John', '=')
->process( $_POST )
->json();