MySQL LEFT OUTER JOIN shenanigans

[Teapot]

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:

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:

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]

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

 

[Teapot]

I cannot be sure, to be honest - I think the problem is less with the SQL and more with how XenForo is handling the data it returns.

If anyone wants to look at my code, I've temporarily mirrored the library/ side of it on GitHub: https://github.com/UppityTeapot/XF-CreativeCorner

 

[Mouth]

Try fetchAllKeyed instead of fetchRow ?

 

[Teapot]

Try fetchAllKeyed instead of fetchRow ?

Didn't appear to make any difference at all, even after changing all references of $work['key'] to $work[1]['key]

Thanks anyway

 

[Bob]

try work.* instead of just * on your select.

 

[Teapot]

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!