Maria DB returning all user dialogues regardless of the dialogue clicked on.

[turf212]

When someone goes into the dialogue activity they are presented with every dialogue they have created, even if they are within other courses. The issue stems from the class conversations_by_author where $this->baseql is defined in line setup() function. The SQL comes out looking something like :

SELECT u.id AS userid, dc.subject, dc.dialogueid, dm.conversationid, dm.conversationindex, dm.authorid, dm.body, dm.bodyformat, dm.attachments, dm.state, dm.timemodified, (SELECT COUNT(dm.id) FROM mdl_dialogue_messages dm WHERE dm.conversationid = dc.id AND dm.state IN ('open','closed')) - (SELECT COUNT(df.id) FROM mdl_dialogue_flags df WHERE df.conversationid = dc.id AND df.flag = 'read' AND df.userid = 275) AS unread FROM mdl_user u JOIN mdl_dialogue_participants dp ON dp.userid = u.id JOIN mdl_dialogue_conversations dc ON dc.id = dp.conversationid JOIN mdl_dialogue_messages dm ON (dm.conversationid = dp.conversationid AND u.id = dm.authorid) JOIN (SELECT dm.conversationid, MAX(dm.conversationindex) AS conversationindex FROM mdl_dialogue_messages dm WHERE dm.dialogueid = 2007 AND dm.state = 'open' GROUP BY dm.conversationid) lastmessage ON (dm.conversationid = lastmessage.conversationid AND dm.conversationindex = lastmessage.conversationindex) JOIN (SELECT dp.conversationid FROM mdl_dialogue_participants dp WHERE dp.userid = 275) isparticipant ON isparticipant.conversationid = dc.id

(I've put the variables into the SQL instead of the placeholders from when I was testing - these would obviously be different on your site)

It seems that the join containing dm.dialogueid isn't filtering everything out for me, however if the SQL is changed to: mdl_dialogue_messages dm WHERE dm.conversationid = dc.id AND dm.state IN ('open','closed')) - (SELECT COUNT(df.id) FROM mdl_dialogue_flags df WHERE df.conversationid = dc.id AND df.flag = 'read' AND df.userid = 275) AS unread FROM mdl_user u JOIN mdl_dialogue_participants dp ON dp.userid = u.id JOIN mdl_dialogue_conversations dc ON dc.id = dp.conversationid JOIN mdl_dialogue_messages dm ON (dm.conversationid = dp.conversationid AND u.id = dm.authorid) JOIN (SELECT dm.conversationid, MAX(dm.conversationindex) AS conversationindex FROM mdl_dialogue_messages dm WHERE dm.dialogueid = 2007 AND dm.state = 'open' GROUP BY dm.conversationid) lastmessage ON (dm.conversationid = lastmessage.conversationid AND dm.conversationindex = lastmessage.conversationindex) JOIN (SELECT dp.conversationid FROM mdl_dialogue_participants dp WHERE dp.userid = 275) isparticipant ON isparticipant.conversationid = dc.id WHERE dm.dialogueid = 2007

to make it more explicit - it works.

The patch I added to the code is in classes/conversations_by_author.php where I added in the following before the $this->fields definition in the setup() function:

        // Fix for returning everything.
        $this->params['thisdialogueid'] = $this->dialogue->activityrecord->id;
        $this->basesql .= " WHERE dm.dialogueid = :thisdialogueid ";

        $this->fields = array('userid' => 'u.id AS userid',
                              'subject' => 'dc.subject',
                              'dialogueid' => 'dc.dialogueid',
                              'conversationid' => 'dm.conversationid',

FYI this is happening on both 3,9 latest version and the 3.11 latest version - with the only common variable being the database behind it.

Many thanks

Derick *edited as code wasn't being displayed properly.

[turf212]

looks like this is a duplicate of #91 - closing this one as all of the discussions are already there.

[danmarsden]

thanks @turf212 - feel free to submit a pull request with the fix!