Complex Join Query

mindtheproduct

I'm attempting the following join but getting an error. Bit stuck and appreciate any ideas on where I'm going wrong.

// Build our Editor instance and process the data coming from _POST
                Editor::inst( $db, 'mtp_event_schedule_speakers', 'speaker_id' )
                ->fields(
                        Field::inst( 'mtp_event_schedule_speakers.speaker_profile_id' ),
                        Field::inst( 'mtp_event_schedule_speakers.speaker_name' ),
                        Field::inst( 'mtp_event_schedule_speakers.speaker_bio_small' ),
                        Field::inst( 'mtp_event_schedule_speakers.speaker_bio_large' ),
                        Field::inst( 'mtp_event_schedule_speakers.speaker_avatar' ),
                        Field::inst( 'mtp_event_schedule_speakers.speaker_id' )->setValue( $day_id )

                )
                ->leftJoin( 'mtp_event_schedule_day_meta as meta',
                        'mtp_event_schedule_speakers.speaker_id', '=',
                        'meta.meta_value AND
                        (meta.meta_key = "speaker_id")'
                )
                ->where('mtp_event_schedule_day_meta.meta_value', $speaker_id )
                ->debug(true)
                ->process( $_POST )
                ->json();

I've referenced here: https://datatables.net/forums/discussion/comment/81166/#Comment_81166

error: DataTables warning: table id=mtp_event_schedule_speakers - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Thanks!

cstooch

I unfortunately don't know the proper syntax using DataTables leftJoin method, but I am pretty certain this is where the problem is:

->leftJoin( 'mtp_event_schedule_day_meta as meta', 'mtp_event_schedule_speakers.speaker_id', '=', 'meta.meta_value AND (meta.meta_key = "speaker_id")' )

I actually found a thread here that says that DataTables doesn't really support complex conditions (i.e. more than one condition on a join) without a teensy bit of work, which looks like it's explained here... good luck!!

https://datatables.net/forums/discussion/30273/leftouterjoin-complex

mindtheproduct

Cheers cstooch, yep thats where the issue is. I've fixed it now. I needed to check two conditions for the join:

1. meta_key = "speaker_id"
2. meta_value = the id

Here's what I used

->leftJoin( 
                        'mtp_event_schedule_day_meta',
                        'mtp_event_schedule_speakers.speaker_id', '=',
                        'mtp_event_schedule_day_meta.meta_value AND mtp_event_schedule_day_meta.meta_key = "speaker_id"'
                )
                ->where('mtp_event_schedule_day_meta.day_id', $day_id )