Problem with SQL AND in Leftjoin - wont write

nigelb

Hi

I am a complete hack when it comes to this sort of thing, but have spent a couple of days trying to solve this problem.

I have a Questions table and Responses Table.

Responses is Left joined to Questions and I have this statement to return all records from Questions and the records from Responses WHERE the Response belongs to the User - pretty simple stuff.

->leftJoin('checklist_responses_tbl', 'checklist_responses_tbl.checklist_question_id' , ' = ', 'checklist_questions_tbl.checklist_question_id AND (checklist_responses_tbl.checklist_assignment_id = "'.$thisUser.'")')

However, the DataTable shows up fine (I can see all the questions, and the responses that $thisUser has submitted) but when I update a value to Responses, the update fails (If I update a Questions value it succeeds)

I cant work out where i am going wrong. Any help would be greatly appreciated.

allan

What are the fields you are trying to write? You are showing a list of questions and you want to write something to the responses table?

Allan

nigelb

correct, I want to leftjoin questions to responses (So I see a full list of questions, and only the responses for that user (called an assignment in the table).
The code that works is this:

Editor::inst( $db, 'checklist_questions_tbl', 'checklist_question_id')
    ->field(
        Field::inst( 'checklist_questions_tbl.checklist_question_id' ),
        Field::inst( 'checklist_questions_tbl.checklist_question_item' ),
        Field::inst( 'checklist_questions_tbl.checklist_question_action' ),
        Field::inst( 'checklist_questions_tbl.checklist_question_metric' ),
        Field::inst( 'checklist_questions_tbl.checklist_question_status' ),
        Field::inst( 'checklist_responses_tbl.checklist_assignment_id' )->setvalue($thisAssignment),
        Field::inst( 'checklist_responses_tbl.checklist_response_item_checked' )->set( true ),
        Field::inst( 'checklist_responses_tbl.checklist_response_item_notes' )->set( true )
        
    )
 ->leftJoin( 'checklist_responses_tbl', 'checklist_responses_tbl.checklist_question_id' , ' = ', 'checklist_questions_tbl.checklist_question_id' )
    ->where( function ( $q ) use($thisAssignment) {
    $q->where( 'checklist_responses_tbl.checklist_assignment_id',$thisAssignment)
        ->or_where( function ( $r ) {
        $r->where( 'checklist_responses_tbl.checklist_assignment_id', null );
    });     
    })
...etc

But if I change the LeftJoin to:

->leftJoin('checklist_responses_tbl', 'checklist_responses_tbl.checklist_question_id' , ' = ', 'checklist_questions_tbl.checklist_question_id AND (checklist_responses_tbl.checklist_assignment_id = "'.$thisAssignment.'")')

It will no longer allow me to update / edit the checklist_responses_tbl

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

The left join in Editor doesn't currently support complex expressions I'm afraid. It's just a straightforward comparison between two fields. The AND expression there is what is throwing it off.

If I've understood the syntax correctly, you are adding a condition to try and ensure that it will only write on the submitted id? That shouldn't be required. If you submit the primary key value for the joined table as part of your Editor form, it should be able to write to the joined table. That said, there is actually an error in 1.8.0 which causes issues with that in PHP (invalid SQL). 1.8.1 resolves that and I'm just about to tag and release that.

Allan