WHERE: how to select max ID

Hello,
I'm working on WHERE conditions field in CCv4
my goal is to select only the max ID for each user_id
(user_id works) (but MAX ID not)

<?php
$user = JFactory::getUser(); 
return array('testresults.user_id' => $user->id );
return array('testresults.id' => SELECT MAX('id');
?>

but it cause sintax error....any idea on how to solve?

Many thanks🙂

Hi sortby,

Please see this FAQ - the Complex query method at the end of the FAQ might help here.

Bob

Hi to all,
I have just built a query with a complex where statement. Here below the code:

<?php

$today = date ('y-m-d'); // data di oggi
$diff = $form->data['scadenze']['cb_scadenza-cert'] - $today;

return array( 'cb_scadenza_cert !=' => '' ,'cb_scadenza_cert' => $diff '<30'  );
// return array ('cb_scadenza_cert' => $diff '<30' );


?>

My aim is to list only records in which the difference between the date in cb_scadenza_cert and $today is less than 30, but actually I get as a result also records in which cb_scadenza_cert field is empty. How can I exclude it? THanks a lot

Hi davideiandoli75,

If you turn the CC Debugger on then you should be able to see the query that is being built - also the FAQ linked from the post before yours may help you.

Bob

Here below the code I used to solve my question:

<?php
$today = date ('y-m-d'); // data di oggi
$diff = $form->data['scadenze']['cb_scadenza-cert'] - $today;
if  ($diff <= 30) {
    return array('cb_scadenza_cert');
} else {
    return array('');
}
?>

This could be added in the Faq, as it is important to know that you can build a where statement by means of an if-clause 😀

Hi davideiandoli75,

I added a short example at the end of the FAQ

Bob