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


Discussion in 'XenForo Development Discussions' started by Teapot, Mar 10, 2013.

  1. Teapot

    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:

    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')
                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 = ?
    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?
  2. Shamil

    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?
  3. Teapot

    Teapot Well-Known Member

    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
  4. Mouth

    Mouth Well-Known Member

    Try fetchAllKeyed instead of fetchRow ?
  5. Teapot

    Teapot Well-Known Member

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

    Thanks anyway :)
  6. Bob

    Bob Well-Known Member

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

    Teapot Well-Known Member

    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!

Share This Page