1
0
-1

Hi,

 

I'm using the following code to get a list of submissions:

List<Submission> submissionList = DaoFactory.getSubmissionDao().getAllSubmissionsByFilter(filter, "submitDateString", cutOffDate)

However I'm not able to find what is the correct dateField to use in the query.

Also wondering what is the cutOffDate - is it to find submissions from the past up to that date, or is it to find submissions after that date up to the latest one?

 

an alternative would be to define a filter that only searches for a particular date of submission or a date range, but again I need to understand how to define the filter in terms of what field is defined for submission date.

 

Any guidance would be much appreciated.

 

Thanks

Mark

    CommentAdd your comment...

    2 answers

    1.  
      1
      0
      -1

      Hi Mark,

      I believe the date field you want is "timeSubmission".

      cutoffDate is used in a less-than expression, so the constraint should  equate to 'where timeSubmission < cutoffDate'.

      1. Mark Murray

        Hi Bill,

        that seemed to do the trick. I got the dateField to work.

        However, the groovy script only returns a limit of 1000 records, but based on a cut off date it returns the oldest records up to the cut off date.

        What I am trying to find is the most recent records, so I'm trying to work out how to get the records 'after' a specified date.

        if there was less than 1000 records I could get what I need, but because there are so many the existing function does not work.

        Any ideas how to overcome this?

         

        Thanks

        Mark

      2. Mark Murray

        Hi Bill,

        I found that the SubmissionDao has a method:

        getFormSubmissions(Form form, int fetchLimit)

         

        Which will - "Return the most recent non-test form submissions for the given form."

         But I'm not sure how to define the Form object to use as the parameter.

         

        Thanks

        Mark

      3. Mark Murray

        Got it!

        You can use FormDao to 'getFormByFormCode(String formName)

        That seems to d the trick.

         

        Mark

      4. Bill Frost

        Nice one!  Glad you got it sorted.

        Bill

      5. Mark Murray

        Hi again.

        I'm now trying to do a query using the method below and a filter:

        getAllSubmissionsByFilter(filter, null, null)
         
        String filter = Submission.FORM_CODE_PROPERTY + " = \"new-partner-member\" and " + Submission.FORM_STATUS_PROPERTY + " = \"Completed\" and " + Submission.TIME_LAST_USER_ACTIVITY_PROPERTY + " like \"2017-06-01\""

        but I'm not sure that the date part of the filter is correct.

        Does the property have a time component?

        I'd like to get all submissions for a given date, or possibly a range.

        Is there any documentation about how to build the filter query?

        Thanks

        Mark

      CommentAdd your comment...
    2.  
      1
      0
      -1

      Hi Mark,

      The expression is parsed by Cayenne's Expression.fromString method.  You can see some information at http://cayenne.apache.org/docs/3.1/cayenne-guide/expressions.html

      'like' expressions would normally include '%' somewhere, but the correct syntax is probably database specific.  You might need to do some kind of string conversion to use like against a datetime column.  You might also need to investigate truncating the database field to a date without time e.g. Oracle's trunc function or MySql's datepart.

      Using < and > comparisons is another method to disregard the time portion of a datetime column.  You could also look at using 'between' for date ranges, instead of 'like'

      Regards,

      Bill

      1. Mark Murray

        Hi Bill,

        that sounds just like what I was looking for - lots more details.

        I'll do a bit more reading and experimenting, and let you know how it goes.

         

        Thanks

      CommentAdd your comment...