Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=10547

Think I fixed sorting (and performance problems for non-superadmin users)

Almost perfect!

When trying to sort by Surveys column I get:

<h1>CDbException</h1>
<p>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'survey'.. The SQL statement executed was: SELECT TOP 25 [p].*, [luser].[full_name] AS [ownername], [attribute1].[value] AS [a1], [attribute2].[value] AS [a2]
FROM [participants] [p]
left join users luser ON luser.uid=p.owner_uid
LEFT JOIN participant_attribute attribute1 ON attribute1.participant_id=p.participant_id AND attribute1.attribute_id=1
LEFT JOIN participant_attribute attribute2 ON attribute2.participant_id=p.participant_id AND attribute2.attribute_id=2
ORDER BY [survey] ASC (C:\limesurvey\framework\db\CDbCommand.php:528)</p><pre>#0 C:\limesurvey\framework\db\CDbCommand.php(390): CDbCommand->queryInternal('fetchAll', Array, Array)
#1 C:\limesurvey\application\models\Participants.php(264): CDbCommand->queryAll()
#2 C:\limesurvey\application\controllers\admin\participantsaction.php(1035): Participants->getParticipants('1', '25', Array, 'survey asc', NULL)
#3 [internal function]: participantsaction->getParticipants_json()
#4 C:\limesurvey\framework\web\actions\CAction.php(107): ReflectionMethod->invokeArgs(Object(participantsaction), Array)
#5 C:\limesurvey\application\core\Survey_Common_Action.php(82): CAction->runWithParamsInternal(Object(participantsaction), Object(ReflectionMethod), Array)
#6 C:\limesurvey\framework\web\CController.php(309): Survey_Common_Action->runWithParams(Array)
#7 C:\limesurvey\framework\web\CController.php(287): CController->runAction(Object(participantsaction))
#8 C:\limesurvey\framework\web\CController.php(266): CController->runActionWithFilters(Object(participantsaction), Array)
#9 C:\limesurvey\application\controllers\AdminController.php(159): CController->run('participants')
#10 C:\limesurvey\framework\web\CWebApplication.php(276): AdminController->run('participants')
#11 C:\limesurvey\framework\web\CWebApplication.php(135): CWebApplication->runController('admin/participa...')
#12 C:\limesurvey\framework\base\CApplication.php(162): CWebApplication->processRequest()
#13 C:\limesurvey\index.php(178): CApplication->run()
#14 {main}</pre>

And when trying to sort by custom attribute column 'Office', I get (also get a similar error when trying to sort by my other custom attribute column):

<h1>CDbException</h1>
<p>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '1'.. The SQL statement executed was: SELECT TOP 25 [p].*, [luser].[full_name] AS [ownername], [attribute1].[value] AS [a1], [attribute2].[value] AS [a2]
FROM [participants] [p]
left join users luser ON luser.uid=p.owner_uid
LEFT JOIN participant_attribute attribute1 ON attribute1.participant_id=p.participant_id AND attribute1.attribute_id=1
LEFT JOIN participant_attribute attribute2 ON attribute2.participant_id=p.participant_id AND attribute2.attribute_id=2
ORDER BY [1] ASC (C:\limesurvey\framework\db\CDbCommand.php:528)</p><pre>#0 C:\limesurvey\framework\db\CDbCommand.php(390): CDbCommand->queryInternal('fetchAll', Array, Array)
#1 C:\limesurvey\application\models\Participants.php(264): CDbCommand->queryAll()
#2 C:\limesurvey\application\controllers\admin\participantsaction.php(1035): Participants->getParticipants('1', '25', Array, '1 asc', NULL)
#3 [internal function]: participantsaction->getParticipants_json()
#4 C:\limesurvey\framework\web\actions\CAction.php(107): ReflectionMethod->invokeArgs(Object(participantsaction), Array)
#5 C:\limesurvey\application\core\Survey_Common_Action.php(82): CAction->runWithParamsInternal(Object(participantsaction), Object(ReflectionMethod), Array)
#6 C:\limesurvey\framework\web\CController.php(309): Survey_Common_Action->runWithParams(Array)
#7 C:\limesurvey\framework\web\CController.php(287): CController->runAction(Object(participantsaction))
#8 C:\limesurvey\framework\web\CController.php(266): CController->runActionWithFilters(Object(participantsaction), Array)
#9 C:\limesurvey\application\controllers\AdminController.php(159): CController->run('participants')
#10 C:\limesurvey\framework\web\CWebApplication.php(276): AdminController->run('participants')
#11 C:\limesurvey\framework\web\CWebApplication.php(135): CWebApplication->runController('admin/participa...')
#12 C:\limesurvey\framework\base\CApplication.php(162): CWebApplication->processRequest()
#13 C:\limesurvey\index.php(178): CApplication->run()
#14 {main}</pre>

Seems sql express is picky again ;-) I didn't get my test setup running using xampp and sqlexpress so i have to rely on you to test the mssql :/

The attributes fail for me too, the other probably need a table prefixed. If will do another commit shortly.

No Problem,

I've just had a look at the columns of the tables in the query and I can't find a survey column in my DB?

SELECT TOP 25 [p].*, [luser].[full_name] AS [ownername], [attribute1].[value] AS [a1], [attribute2].[value] AS [a2] FROM [participants] [p] left join users luser ON luser.uid=p.owner_uid LEFT JOIN participant_attribute attribute1 ON attribute1.participant_id=p.participant_id AND attribute1.attribute_id=1 LEFT JOIN participant_attribute attribute2 ON attribute2.participant_id=p.participant_id AND attribute2.attribute_id=2 ORDER BY [survey] ASC

Yeah found it, no mssql issue here didn't need the table prefix. Survey(count) is a calculated columnjust like language and ownername. Not sure if I can include it but will see what I can do. Will push a fix for the attributes first.

Fix committed to 2.1 branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&amp;id=10550

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&amp;id=10551

Fix committed to 2.1 branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&amp;id=10553

That seems to have sorted the custom attribute fields, thanks :)

There seems to be a slight issue with overlap on pages however when sorting by custom attributes. I sorted by custom attribute department and the last two entries on the bottom of page 1 are on the top of Page 2 ... moving past page 2 isn't possible due to the pageing bug reported in a seperate log.

Edit : Paging bug only seems to happen now when sorted by a custom attribute - Great stuff!!!

I've also noticed another issue which I don't think was present before but may be unrelated ... tell me if you would rather I log a seperate bug?

When adding participants from a survey to the CPD it will only add 1000 even if more than this number are selected. eg I selected 1,984, selected all the entries and selected add participants to central database. The confirmation told me it had added 1,000 and when I checked, only 1,000 had been created in the CPD (Note duplicates did not already exist and this process was in Firefox).

I noticed that editing a participant doesn't work (anymore?). Do you know if it worked before? Before i push any other fixes I have ready, I will see if i can fix that.

Please open another issue for the adding participants, that way we don't loose track of it hidden inside the comments.

I'm not sure to be honest ... when I try editing I get the error 'CDbCommand failed to execute the SQL statement: SQLSTATE[07002]: ...'

I'll open another ticket for the other bug :)

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&amp;id=10563

Fix committed to 2.1 branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&amp;id=10564

Ok it took some iterations, but all fields should be sortable now and performance should be ok even with large numbers.

Sounds great, I'll try and make some time later this afternoon to test this and let you know.

Feel free to open a new bugreport after next release (probably tuesday) if issue is still around

I've now had time to test this and there seems to be another issue introduced by the fix (let me know if you'd rather I log a seperate bug report):

Add participants from the CPD to a survey with custom attributes and map these across, the following error is then displayed:

<h1>CDbException</h1>
<p>CDbCommand failed to execute the SQL statement: SQLSTATE[22018]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting the nvarchar value 'attribute_1' to data type int.. The SQL statement executed was: SELECT [value]
FROM [participant_attribute]
WHERE participant_id = :participant_id AND attribute_id = :attrid. Bound with participant_id='21706911-b4ab-4d69-a12e-6fd79adaa0eb', attrid='attribute_1' (C:\limesurvey_test\framework\db\CDbCommand.php:528)</p><pre>#0 C:\limesurvey_test\framework\db\CDbCommand.php(408): CDbCommand->queryInternal('fetch', Array, Array)
#1 C:\limesurvey_test\application\models\Participants.php(924): CDbCommand->queryRow()
#2 C:\limesurvey_test\application\models\Participants.php(895): Participants->updateTokenAttributeValue('897685', '21706911-b4ab-4...', 'attribute_1', 17)
#3 C:\limesurvey_test\application\controllers\admin\participantsaction.php(1753): Participants->copytosurveyatt('897685', Array, Array, '21706911-b4ab-4...', 'false', 'false', 'false', 'true')
#4 [internal function]: participantsaction->addToTokenattmap()
#5 C:\limesurvey_test\framework\web\actions\CAction.php(107): ReflectionMethod->invokeArgs(Object(participantsaction), Array)
#6 C:\limesurvey_test\application\core\Survey_Common_Action.php(82): CAction->runWithParamsInternal(Object(participantsaction), Object(ReflectionMethod), Array)
#7 C:\limesurvey_test\framework\web\CController.php(309): Survey_Common_Action->runWithParams(Array)
#8 C:\limesurvey_test\framework\web\CController.php(287): CController->runAction(Object(participantsaction))
#9 C:\limesurvey_test\framework\web\CController.php(266): CController->runActionWithFilters(Object(participantsaction), Array)
#10 C:\limesurvey_test\application\controllers\AdminController.php(159): CController->run('participants')
#11 C:\limesurvey_test\framework\web\CWebApplication.php(276): AdminController->run('participants')
#12 C:\limesurvey_test\framework\web\CWebApplication.php(135): CWebApplication->runController('admin/participa...')
#13 C:\limesurvey_test\framework\base\CApplication.php(162): CWebApplication->processRequest()
#14 C:\limesurvey_test\index.php(178): CApplication->run()
#15 {main}</pre>

Also not sure if you're expecting to see the editing bug fixed, gives the error below for me:

CDbCommand failed to execute the SQL statement: SQLSTATE[07002]: [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error. The SQL statement executed was: UPDATE [participants] SET [participant_id]=:participant_id, [firstname]=:firstname, [lastname]=:lastname, [email]=:email, [language]=:language, [blacklisted]=:blacklisted, [owner_uid]=:owner_uid WHERE participant_id = :participant_id. Bound with :participant_id='8f4a509c-f377-4970-aacc-c00a407cb587', :firstname='Fred', :lastname='Smith-test', :email='fred.smith@email.com', :language='en', :blacklisted='N', :owner_uid=1 (C:\limesurvey_test\framework\db\CDbCommand.php:354)

With this last commit all seems to be working for me. Could you please wait for the new release (tomorrow) and then open a new bugreport if you still have issues? Thank you!

No problem at all, I'll grab the latest release when it's published and test all my outstanding issues then.

Dave

LimeSurvey 2.0+ build 121204 released.