Optimize Subselect Search query

Question

In my MYSQL database I have multiple tables like this:

reference:

------------------------------
| id | title   | subtitle    |
| 1  | Test    | Just a test |
| 2  | Another | Second      |
| 3  | Last    | Third       |
------------------------------

author:

-----------------------------
| id | firstname | lastname |
| 1  | Peter     | Pan      |
| 2  | Foo       | Bar      |
| 3  | Mr.       | Handsome |
| 4  | Steve     | Jobs     |
-----------------------------

keyword:

----------------
| id | keyword |
| 1  | boring  |
| 1  | lame    |
----------------

reference_author_mm:

---------------------------
| uid_local | uid_foreign |
| 1         | 1           |
| 1         | 2           |
| 2         | 1           |
| 2         | 2           |
| 2         | 4           |
| 3         | 1           |
| 3         | 2           |
| 3         | 4           |
---------------------------

All tables have a n:m relation table to the references table. With one query I want to SELECT all references and filter/order them by author name or keywords etc. So the output I want is something like this:

User searched by title:

 ------------------------------------------------------------------------------
| id | title   | subtitle    | authors                        | keywords     |
| 1  | Test    | Just a test | Peter Pan, Foo Bar             | boring       |
| 2  | Another | Second      | Peter Pan, Foo Bar, Steve Jobs | boring, lame |
| 3  | Last    | Third       | Peter Pan, Foo Bar, Steve Jobs | boring, lame |
-------------------------------------------------------------------------------

At this moment I have a very long construct that is not quite working:

SELECT r.* , Authors.author AS authors, Keywords.keyword AS keywords
FROM references AS r
LEFT OUTER JOIN (
    SELECT r.pid AS pid,
        GROUP_CONCAT(CONCAT_WS(\' \', CONCAT(p.lastname, \',\'), p.prefix, p.firstname) SEPARATOR \'; \') AS author
    FROM reference AS r
    LEFT JOIN reference_author_mm r2p ON r2p.uid_local = r.uid
    LEFT JOIN author p ON p.uid = r2p.uid_foreign
    GROUP BY r.pid
) Authors ON r.pid = Authors.pid

This LEFT OUTER JOIN statement exists multiple times for every n:m relation to references. What would be the easiest way to query these tables. Keep in mind that I need to e.g. ODER BY the authors or search through the keywords of a reference.

Edit:

The new query I tried:

SELECT r.*,
    GROUP_CONCAT(CONCAT_WS(\' \', CONCAT(a.lastname, \',\'), a.prefix, a.firstname) SEPARATOR \'; \') AS authors,
    GROUP_CONCAT(CONCAT_WS(\' \', k.name)) AS keywords,
    GROUP_CONCAT(CONCAT_WS(\' \', c.name)) AS categories,
    GROUP_CONCAT(CONCAT_WS(\' \', p.name)) AS publishers
FROM reference AS r
LEFT OUTER JOIN (
    SELECT * FROM reference_author_mm AS rha
        INNER JOIN author AS a ON a.uid = rha.uid_foreign
) AS a ON a.uid_local = r.uid
LEFT OUTER JOIN (
    SELECT * FROM reference_keyword_mm AS rhk
        INNER JOIN keywords AS k ON k.uid = rhk.uid_foreign
) AS k ON k.uid_local = r.uid

Answer 1

I tried to make my query as clear as possible according to the table struture you specified in your question (not what I could see on your queries). You will probably need to change some field/table name.

You can have a look on the query result here : SQLFiddle demo

SELECT
    r.id
    ,r.title
    ,r.subtitle
    ,ra.authors
    ,rk.keyworks
FROM
    reference r
    LEFT JOIN
        (SELECT
            r.id
            ,GROUP_CONCAT(CONCAT(a.firstname, ' ', a.lastname) ORDER BY CONCAT(a.firstname, ' ', a.lastname) SEPARATOR ', ') as authors
        FROM
            reference r
            LEFT JOIN reference_author_mm r2a
                ON r.id = r2a.uid_local
            LEFT JOIN author a 
                ON a.id = r2a.uid_foreign
        GROUP BY
            r.id) ra
        ON ra.id = r.id
    LEFT JOIN
        (SELECT
            r.id
            ,GROUP_CONCAT(k.keyword ORDER BY k.keyword SEPARATOR ', ') as keyworks
        FROM
            reference r
            LEFT JOIN reference_keyword_mm r2k
                ON r.id = r2k.uid_local
            LEFT JOIN keyword k
                ON k.id = r2k.uid_foreign
        GROUP BY
            r.id) rk
        ON rk.id = r.id 
ORDER BY
    r.id

If you need to order authors name by lastname, just change CONCAT(a.firstname, ' ', a.lastname) to CONCAT(a.lastname, ' ', a.firstname)

If you have any issue, feel free to update the table structure in my sqlfiddle demo, and leave me a comment that explain what is the issue you are facing and give me examples.