Complex Filter Query

Hi

I would like to create a complex filter query in Chronoconnectivity V6 like below:

(`modified` >= 'filter_field_value' AND `modified` <= 'filter_field_value') AND `chapter` = 'filter_field_value'

I am using a single Read function to display the filtered values as well. So if the Filter form fields are empty the WHERE condition is to be empty else the WHERE condition should be filled as per the filter fields.

I tried the following php code in the WHERE condition of my function. But the resultant query has no WHERE condition.

<?php
if (!empty($this->data['fromdate']) AND  !empty($this->data['todate']) AND !empty($this->data['chapterselect'])) {
   return array (":(CorpTrans.modified >= '$this->data["fromdate"]' AND CorpTrans.modified = '$this->data["todate"]') AND CorpTrans.chapter = '$this->data["chapterselect"]'");
}
?>

I also tried the following format, using /- for skipping empty fields.

CorpTrans:{data:chapterselect}/-
AND
(
CorpTrans/>=:{data:fromdate}/-
AND
CorpTrans/<=:{data:todate}/-
)

But this gives the following error when the view is initially loaded with all empty conditions:

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 'AND ( AND )' at line 1

Can you guide me as to how to build the required query? The important aspect is if filter is empty or any filter field value is empty then also the query has to work based in the remaining conditions.

Thanks,
Anu

Hi Anu,

In the where conditions box:

(
Model.field1:{data:x}
AND
Model.field2:{data:y}
)
AND
Model.field3:xyz

You can use any combination of syntax

Best regards,
Max