Thread Details
  • State Verified Answer
  • Replies 1 reply
  • Subscribers 366 subscribers
  • Views 734 views
  • Users 0 members are here
Forum Actions

SugarQuery Having Max(datefield) < xxxx-xx-xx

Stijn Vermeulen

Hi,

I need to create a query which selects contacts which only having cases older then one year.

I managed to create a working MySQL query, but how to convert it to a SugarQuery? Anyone any experience with this type of query?

MySQL

SELECT contacts.id, MAX(jt0_cases_cstm.dat_start_c)
FROM contacts
INNER JOIN contacts_cases contacts_cases ON (contacts.id = contacts_cases.contact_id) AND (contacts_cases.deleted = 0)
INNER JOIN cases jt0_cases ON (jt0_cases.id = contacts_cases.case_id) AND (jt0_cases.deleted = 0)
LEFT JOIN cases_cstm jt0_cases_cstm ON jt0_cases_cstm.id_c = jt0_cases.id
LEFT JOIN contacts_cstm contacts_cstm ON contacts_cstm.id_c = contacts.id
WHERE (contacts.deleted = 0)
HAVING (MAX(jt0_cases_cstm.dat_start_c) <= '2022-11-01')

SugarQuery

$SQ = new SugarQuery();
$SQ->select(array("id"));
$SQ->from(BeanFactory::newBean("Contacts"));
$cases = $SQ->join('cases')->joinName();
$havingCondition = new SugarQuery_Builder_Condition($SQ);
// conceptual code, not working...
// $havingCondition->max("$cases.dat_start_c")->setOperator('<')->setValue('2022-11-01');
// $havingCondition->setField("max(\"$cases.dat_start_c\")")->setOperator('<')->setValues('2022-11-01');
$SQ->having($havingCondition);