Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
Clear All
new posts
Previous template Next
    #1

    Grid pagination does not work with this customSQL

    Hello,

    using: SmartClient Version: v8.3p_2013-01-09/PowerEdition Deployment (built 2013-01-09)

    I have a fairly complex SQL query, written with the SQL templating system like so:
    Code:
     <customSQL> <![CDATA[
	  SELECT * FROM (
		  SELECT
		  
		      Lecture.Lecture_id as Lecture_id,
		      Lecture_presence_Student.presence_id as presence_id,
	  	      Student.SaasCustomer_id as SaasCustomer_id,		  
		  	  'Les' as unifiedType,
		      Student.firstname as unifiedFirstname, 
		      Student.tussenvoegsel as unifiedTussenvoegsel, 
		      Student.surname as unifiedSurname, 
		      Lecture.lectureName, 
		      Lecture.startDate, 
		      Lecture.endDate, 
		      Lecture_presence_Student.status as unifiedStatus,
		      Lecture_presence_Student.reason as unifiedReason, 
		      Company.companyName as unifiedCompanyName, 
		      CourseEdition.editionName as unifiedEditionName,
		      ContactPerson.ContactPerson_id as ContactPerson_id,
		      Student.Student_id as Student_id
		  FROM
			  Lecture_presence_Student 
			  	INNER JOIN Student ON Lecture_presence_Student.Student_id=Student.Student_id 
	      		INNER JOIN Lecture ON Lecture.Lecture_id = Lecture_presence_Student.Lecture_id 
	      		INNER JOIN CourseEdition ON Lecture.CourseEdition_id = CourseEdition.CourseEdition_id
	      		LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
	      		LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
  	  UNION
	  	  SELECT
	  	  	  ExamMoment.ExamMoment_id as Lecture_id,
	  	  	  -1*Exam_participants_Student.participants_id as presence_id,
	  	      Student.SaasCustomer_id as SaasCustomer_id,
	  	  	  'Examen' as unifiedType,
		      Student.firstname as unifiedFirstname, 
		      Student.tussenvoegsel as unifiedTussenvoegsel, 
		      Student.surname as unifiedSurname, 
		      ExamMoment.emName as lectureName, 
		      ExamMoment.examDateStart as startDate, 
		      ExamMoment.examDateEnd   as endDate, 
		      ExamParticipationToExamMoment.epPresence as unifiedStatus,
		      ExamParticipationToExamMoment.epRemark_ as unifiedReason, 
		      Company.companyName as unifiedCompanyName, 
		      CourseEdition.editionName as unifiedEditionName,
		      ContactPerson.ContactPerson_id as ContactPerson_id,
		      Student.Student_id as Student_id 
		  FROM
	  	  	ExamParticipationToExamMoment
	  	  		INNER JOIN Exam_participants_Student ON Exam_participants_Student.participants_id = ExamParticipationToExamMoment.participants_id
      			INNER JOIN Exam ON Exam_participants_Student.Exam_id = Exam.Exam_id
      			INNER JOIN ExamMoment ON ExamMoment.ExamMoment_id = ExamParticipationToExamMoment.ExamMoment_id
      			INNER JOIN Block ON Exam.Block_id = Block.Block_id
      			INNER JOIN Student ON Exam_participants_Student.Student_id = Student.Student_id
      			LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
      			LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
      			LEFT OUTER JOIN CourseEdition ON CourseEdition.CourseEdition_id = ExamMoment.CourseEdition_id
      			
  	  ) as Lecture_presence_Student
		WHERE ($defaultWhereClause) AND Lecture_presence_Student.unifiedStatus <> 'onverwerkt'
		#if ($criteria.ContactPerson > 0) AND Lecture_presence_Student.ContactPerson_id=$criteria.ContactPerson_id #end
		ORDER BY $defaultOrderClause
		#if ($dsRequest.getEndRow() > -1)
	    LIMIT $dsRequest.getStartRow(), $dsRequest.getEndRow()
	    #end
      ]]></customSQL>
    This query populates a ListGrid.

    The problem is that, it will populate the 0,75 start+endrow values, but upon scrolling it doesn't increase this amount.

    Is it due to the structure of the query, or should this be working fine?

    Also consider the log, maybe it gives away some information:
    Code:
    === 2013-01-21 11:10:52,779 [0-16] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"Lecture_presence_Student",
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_StudentPresenceReport_PresenceGrid_3",
    appID:"builtinApplication",
    operation:"PresenceGridReport",
    oldValues:{
    }
}
=== 2013-01-21 11:10:52,824 [0-16] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2013-01-21 11:10:52,824 [0-16] DEBUG DeclarativeSecurity - DataSource Lecture_presence_Student is not in the pre-checked list, processing...
=== 2013-01-21 11:10:52,825 [0-16] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2013-01-21 11:10:52,825 [0-16] DEBUG DeclarativeSecurity - DataSource Lecture_presence_Student is not in the pre-checked list, processing...
=== 2013-01-21 11:10:52,825 [0-16] DEBUG AppBase - [builtinApplication.PresenceGridReport] No userTypes defined, allowing anyone access to all operations for this application
=== 2013-01-21 11:10:52,825 [0-16] DEBUG AppBase - [builtinApplication.PresenceGridReport] No public zero-argument method named '_PresenceGridReport' found, performing generic datasource operation
=== 2013-01-21 11:10:52,826 [0-16] INFO  SQLDataSource - [builtinApplication.PresenceGridReport] Performing fetch operation with
	criteria: {SaasCustomer_id:1}	values: {SaasCustomer_id:1}
=== 2013-01-21 11:10:52,834 [0-16] INFO  SQLDataSource - [builtinApplication.PresenceGridReport] Executing SQL query on 'Mysql': SELECT * FROM (
		  SELECT
		  
		      Lecture.Lecture_id as Lecture_id,
		      Lecture_presence_Student.presence_id as presence_id,
	  	      Student.SaasCustomer_id as SaasCustomer_id,		  
		  	  'Les' as unifiedType,
		      Student.firstname as unifiedFirstname, 
		      Student.tussenvoegsel as unifiedTussenvoegsel, 
		      Student.surname as unifiedSurname, 
		      Lecture.lectureName, 
		      Lecture.startDate, 
		      Lecture.endDate, 
		      Lecture_presence_Student.status as unifiedStatus,
		      Lecture_presence_Student.reason as unifiedReason, 
		      Company.companyName as unifiedCompanyName, 
		      CourseEdition.editionName as unifiedEditionName,
		      ContactPerson.ContactPerson_id as ContactPerson_id,
		      Student.Student_id as Student_id
		  FROM
			  Lecture_presence_Student 
			  	INNER JOIN Student ON Lecture_presence_Student.Student_id=Student.Student_id 
	      		INNER JOIN Lecture ON Lecture.Lecture_id = Lecture_presence_Student.Lecture_id 
	      		INNER JOIN CourseEdition ON Lecture.CourseEdition_id = CourseEdition.CourseEdition_id
	      		LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
	      		LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
  	  UNION
	  	  SELECT
	  	  	  ExamMoment.ExamMoment_id as Lecture_id,
	  	  	  -1*Exam_participants_Student.participants_id as presence_id,
	  	      Student.SaasCustomer_id as SaasCustomer_id,
	  	  	  'Examen' as unifiedType,
		      Student.firstname as unifiedFirstname, 
		      Student.tussenvoegsel as unifiedTussenvoegsel, 
		      Student.surname as unifiedSurname, 
		      ExamMoment.emName as lectureName, 
		      ExamMoment.examDateStart as startDate, 
		      ExamMoment.examDateEnd   as endDate, 
		      ExamParticipationToExamMoment.epPresence as unifiedStatus,
		      ExamParticipationToExamMoment.epRemark_ as unifiedReason, 
		      Company.companyName as unifiedCompanyName, 
		      CourseEdition.editionName as unifiedEditionName,
		      ContactPerson.ContactPerson_id as ContactPerson_id,
		      Student.Student_id as Student_id 
		  FROM
	  	  	ExamParticipationToExamMoment
	  	  		INNER JOIN Exam_participants_Student ON Exam_participants_Student.participants_id = ExamParticipationToExamMoment.participants_id
      			INNER JOIN Exam ON Exam_participants_Student.Exam_id = Exam.Exam_id
      			INNER JOIN ExamMoment ON ExamMoment.ExamMoment_id = ExamParticipationToExamMoment.ExamMoment_id
      			INNER JOIN Block ON Exam.Block_id = Block.Block_id
      			INNER JOIN Student ON Exam_participants_Student.Student_id = Student.Student_id
      			LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
      			LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
      			LEFT OUTER JOIN CourseEdition ON CourseEdition.CourseEdition_id = ExamMoment.CourseEdition_id
      			
  	  ) as Lecture_presence_Student
		WHERE (('1'='1')) AND Lecture_presence_Student.unifiedStatus <> 'onverwerkt'
		
			    LIMIT 0, 75
	    
=== 2013-01-21 11:10:52,834 [0-16] DEBUG PoolableSQLConnectionFactory - [builtinApplication.PresenceGridReport] Executing pingTest 'select 1 from dual' on connection 824381750
=== 2013-01-21 11:10:52,914 [0-16] DEBUG SQLConnectionManager - [builtinApplication.PresenceGridReport] Returning borrowed connection '824381750'
=== 2013-01-21 11:10:52,914 [0-16] DEBUG SQLDriver - [builtinApplication.PresenceGridReport] About to execute SQL query in 'Mysql' using connection '824381750'
=== 2013-01-21 11:10:52,914 [0-16] INFO  SQLDriver - [builtinApplication.PresenceGridReport] Executing SQL query on 'Mysql': SELECT * FROM (
		  SELECT
		  
		      Lecture.Lecture_id as Lecture_id,
		      Lecture_presence_Student.presence_id as presence_id,
	  	      Student.SaasCustomer_id as SaasCustomer_id,		  
		  	  'Les' as unifiedType,
		      Student.firstname as unifiedFirstname, 
		      Student.tussenvoegsel as unifiedTussenvoegsel, 
		      Student.surname as unifiedSurname, 
		      Lecture.lectureName, 
		      Lecture.startDate, 
		      Lecture.endDate, 
		      Lecture_presence_Student.status as unifiedStatus,
		      Lecture_presence_Student.reason as unifiedReason, 
		      Company.companyName as unifiedCompanyName, 
		      CourseEdition.editionName as unifiedEditionName,
		      ContactPerson.ContactPerson_id as ContactPerson_id,
		      Student.Student_id as Student_id
		  FROM
			  Lecture_presence_Student 
			  	INNER JOIN Student ON Lecture_presence_Student.Student_id=Student.Student_id 
	      		INNER JOIN Lecture ON Lecture.Lecture_id = Lecture_presence_Student.Lecture_id 
	      		INNER JOIN CourseEdition ON Lecture.CourseEdition_id = CourseEdition.CourseEdition_id
	      		LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
	      		LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
  	  UNION
	  	  SELECT
	  	  	  ExamMoment.ExamMoment_id as Lecture_id,
	  	  	  -1*Exam_participants_Student.participants_id as presence_id,
	  	      Student.SaasCustomer_id as SaasCustomer_id,
	  	  	  'Examen' as unifiedType,
		      Student.firstname as unifiedFirstname, 
		      Student.tussenvoegsel as unifiedTussenvoegsel, 
		      Student.surname as unifiedSurname, 
		      ExamMoment.emName as lectureName, 
		      ExamMoment.examDateStart as startDate, 
		      ExamMoment.examDateEnd   as endDate, 
		      ExamParticipationToExamMoment.epPresence as unifiedStatus,
		      ExamParticipationToExamMoment.epRemark_ as unifiedReason, 
		      Company.companyName as unifiedCompanyName, 
		      CourseEdition.editionName as unifiedEditionName,
		      ContactPerson.ContactPerson_id as ContactPerson_id,
		      Student.Student_id as Student_id 
		  FROM
	  	  	ExamParticipationToExamMoment
	  	  		INNER JOIN Exam_participants_Student ON Exam_participants_Student.participants_id = ExamParticipationToExamMoment.participants_id
      			INNER JOIN Exam ON Exam_participants_Student.Exam_id = Exam.Exam_id
      			INNER JOIN ExamMoment ON ExamMoment.ExamMoment_id = ExamParticipationToExamMoment.ExamMoment_id
      			INNER JOIN Block ON Exam.Block_id = Block.Block_id
      			INNER JOIN Student ON Exam_participants_Student.Student_id = Student.Student_id
      			LEFT OUTER JOIN ContactPerson ON ContactPerson.ContactPerson_id = Student.ContactPerson_id
      			LEFT OUTER JOIN Company ON Student.Company_id = Company.Company_id
      			LEFT OUTER JOIN CourseEdition ON CourseEdition.CourseEdition_id = ExamMoment.CourseEdition_id
      			
  	  ) as Lecture_presence_Student
		WHERE (('1'='1')) AND Lecture_presence_Student.unifiedStatus <> 'onverwerkt'
		
			    LIMIT 0, 75
	    
=== 2013-01-21 11:10:53,147 [0-16] INFO  DSResponse - [builtinApplication.PresenceGridReport] DSResponse: List with 75 items
=== 2013-01-21 11:10:53,386 [0-16] DEBUG SQLConnectionManager - About to close PoolableConnection with hashcode "824381750"
=== 2013-01-21 11:10:53,386 [0-16] DEBUG PoolableSQLConnectionFactory - Executing pingTest 'select 1 from dual' on connection 824381750
=== 2013-01-21 11:10:53,409 [0-16] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2013-01-21 11:10:53,409 [0-16] DEBUG RPCManager - DMI response, dropExtraFields: true
=== 2013-01-21 11:10:53,414 [0-16] INFO  Compression - /generatedcode/sc/IDACall: 23834 -> 1474 bytes
    Tags: None
    #2
    Without having looked in detail at your query yet, we would say, take a look at the totalRows values being returned. Maybe it's the row count query that won't work with your complex query, in which case, progressiveLoading mode might fix it.

    Comment

      #3
      I tried both true and false, like so:
      Code:
      <operationBinding requiresRole="ROLE_USER" requiresAuthentication="true" operationType="fetch" operationId="PresenceGridReport" progressiveLoading="true"> 

...
</operationBinding>
      but it didn't make a difference. Also, in the logs, i didn't see the select count(*) type query. Any idea why is it not executing to get the totalRows? Because I think that's the problem; it doesn't realize the complete result set size?

      Comment

        #4
        Take a look at operationBinding.sqlPaging - there are different options allowing you to control whether or not we try to obtain totalRows or not.

        Comment

        Previous template Next
        Working...
        X