• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

MySQL LEFT OUTER JOIN shenanigans

Teapot

Well-known member
#1
Okay, I'm showing my inexperience with MySQL here. I am writing some code that stores data in multiple tables depending on what "type" of content it is. For instance, a row in my main table, cc_work, looks like this:

Code:
work_id: 1
work_type :'writing'
title: 'Test work'
summary: 'Is this working yet?'
I then have separate tables for each work_type - cc_work_art, cc_work_writing, etcetera. Right now I am dealing with JOINing the correct one onto the main database like this:

PHP:
public function getWorkById($workId, $contentType = 'writing')
    {
        return $this->_getDb()->fetchRow('
            SELECT *
            FROM cc_work AS `work`
            INNER JOIN xf_user AS `user`
                ON (`user`.user_id = `work`.user_id)
            LEFT OUTER JOIN cc_work_writing AS `writing`
                ON (`work`.work_id = `writing`.work_id)
            LEFT OUTER JOIN cc_work_art AS `art`
                ON (`work`.work_id = `art`.work_id)
            LEFT OUTER JOIN cc_work_art_file AS `art_file`
                ON (`work`.work_id = `art_file`.work_id)
            WHERE `work`.work_id = ?
            ', $workId);
    }
This works fairly well - it JOINs the tables if they have a work_id matching the one in the main table. As I only write to one table, that works well when viewing the code.

Unfortunately, I have a fairly major bug in my code which is directly caused by these LEFT JOINS. Although displaying the work works correctly, the links to edit, delete, Like, or comment upon a work are all broken - the edit link, for example, is displayed as http://xf.dev/works/edit, instead of the correct http://xf.dev/works/test-work.1/edit. Testing using a writing work type, removing the two other LEFT OUTER JOINS completely solves the problem.

I'm completely lost as to what's causing this to happen - does anyone know what I'm doing wrong?

Thanks for your time :)

EDIT: Can a mod change the thread title to "MySQL LEFT OUTER JOIN on multiple tables breaks links", please?