MySQL LEFT OUTER JOIN shenanigans

Teapot

Well-known member
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?
 

Shamil

Well-known member
I haven't had a massive look, but are you sure that your SQL is your problem if your URI is malformed/incorrect?
 

Teapot

Well-known member
try work.* instead of just * on your select.
That basically led me directly to a solution - you were very close. The problem is solved if I manually select everything except the work_id fields in the other tables!

Thanks so much!
 
Top