Uploaded image for project: 'Cayenne'
  1. Cayenne
  2. CAY-2187

Support for the scalar and aggregate SQL functions in ObjectSelect API

Agile BoardRank to TopRank to BottomAttach filesAttach ScreenshotBulk Copy AttachmentsBulk Move AttachmentsVotersWatch issueWatchersCreate sub-taskLinkCloneLabelsUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Task
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 4.0.M5
    • 4.0.M5
    • Core Library
    • None

    Description

      For now only options to use SQL functions (including aggregate) are:

      1. EJBQL
      2. plain SQL

      Both of it lack any type-checking or support from the API side as user needs to wright and debug queries with plain text.

      So Cayenne needs API for that functionality in order to have more control.
      It is suggested to add following features and new API in ObjectSelect query:

      1. New expressions for function calls and new factory as convenient method to create them:
         
                Expression substrExp = FunctionExpressionFactory.substringExp(Artist.ARTIST_NAME.path(), 10, 15);     
                Expression modExp = FunctionExpressionFactory.modExp(Artist.ARTIST_SALARY.path(), 10);
        
      2. Enhanced Property class with Expression support and explicit type,
        they can be declared in persistent object like normal Cayenne properties or adhoc for specific query:
                Property<String> namePart = Property.create("namePart", substrExp, String.class);
                Property<Long> artistCount = Property.create("artistCount", countExp, Long.class);
                Property<Integer> minSalary = Property.create("minSalary", minExp, Integer.class);
        
      3. New methods in ObjectSelect for specifying return columns for the query:
                   
                // Single column
                long totalCount = ObjectSelect.query(Artist.class)
                                    .column(Artist.ARTIST_COUNT)
                                    .selectOne(context);
                                    
                // Several columns
                List<Object[]> result = ObjectSelect.query(Artist.class)
                                            .columns(artistCount, minSalary, namePart)
                                            .select(context);
                for(Object[] r : result) {
                    long count = (long)r[0];
                    int min = (int)r[1];
                    String namePart = (String)r[2];
                }
        
      4. New having() method in ObjectSelect:
                // Full query example:
                List<Object[]> result = ObjectSelect.query(Artist.class)
                        // result
                        .columns(artistCount, minSalary, namePart)
                        // WHERE clause
                        .where(Artist.DATE_OF_BIRTH.lt(new Date())) 
                        // additional condition in WHERE clause
                        .or(...)                      
                        // HAVING clause
                        .having(namePart.like("P%"))                
                        // additional condition in HAVING clause
                        .or(...)                                    
                        .select(context);
        
                for(Object[] r : result) {
                    long count = (long)r[0];
                    int min = (int)r[1];
                    String name = (String)r[2];
                }
        
                // Aggregate on toMany relationship:
                Property<Long> paintingCountProperty = Artist.PAINTING_ARRAY.count();
                List<Object[]> result2 = ObjectSelect.query(Artist.class)
                        .columns(Artist.ARTIST_NAME, paintingCountProperty)
                        .having(paintingCountProperty.gt(10L))
                        .select(context);
                for(Object[] r : result2) {
                    String artistName = (String)r[0];
                    long paintingCount = (long)r[1];
                }
        

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            ntimofeev Nikita Timofeev
            ntimofeev Nikita Timofeev
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment