gistfile1.sql

SELECT
    concerts.id as id,
    concerts.title as title,
    concerts.summary as summary,
    concerts.s_programme as programme,
    concerts.booking_url as booking_url,
    DATE_FORMAT(concerts.dt, '%W %D %M, %Y') as date,
    DATE_FORMAT(concerts.dt, '%l.%i%p') as time,
    GROUP_CONCAT(DISTINCT CONCAT_WS(' ', composers.firstname, composers.lastname)) as composers,
    GROUP_CONCAT(DISTINCT pieces.piece) as pieces,
    GROUP_CONCAT(DISTINCT CONCAT_WS(' ', performers.p_firstname, performers.p_lastname)) as performer,
    venues.vname as venue,
    concerts.spotify_url as spotify_url,
    concerts.youtube_url as youtube_url,
    (SELECT
        COUNT(p.clip)
     FROM
        concert_pieces cp,
        pieces p
     WHERE p.id = cp.pid
     AND cp.cid=concerts.id
     AND p.clip <> ''
    ) as clipCount,
    (SELECT
        COUNT(p.spotify_url),
        COUNT(p.youtube_url)
     FROM
        concert_pieces cp,
        pieces p
     WHERE p.id = cp.pid
     AND cp.cid=concerts.id
     AND p.spotify_url <> ''
     OR p.youtube_url <> ''
    ) as spotifyCount
FROM concerts
LEFT JOIN concert_pieces ON
    concerts.id = concert_pieces.cid
LEFT JOIN pieces ON
    concert_pieces.pid = pieces.id
LEFT JOIN composers ON
    pieces.composer_id = composers.id
LEFT JOIN concert_performers ON
    concert_performers.cid = concerts.id
LEFT JOIN performers ON
    concert_performers.perf_id = performers.id
LEFT JOIN venues ON
    concerts.vid = venues.vid
LEFT JOIN concert_cats cc ON
    concerts.id = cc.cid
WHERE
    active = 1
AND
    dt > NOW()
AND (
    MATCH({$this->_searchFields})
        AGAINST (:string IN BOOLEAN MODE)
    OR composers.firstname LIKE :wild
    OR composers.lastname LIKE :wild
    OR concerts.s_programme LIKE :wild
    OR piece LIKE :wild
    OR performers.p_firstname LIKE :wild
    OR performers.p_lastname LIKE :wild
    OR venues.vname LIKE :wild
    OR (cc.tour = 1 AND 'tour' LIKE :wild )
    OR (cc.cs = 1 AND 'centre stage' LIKE :wild)
    OR (cc.floof = 1 AND 'floof' LIKE :wild )
    OR (cc.evening = 1 AND 'evening' LIKE :wild )
    OR (cc.matinee = 1 AND 'matinee' LIKE :wild )
    OR (cc.family = 1 AND 'family' LIKE :wild )
    OR (cc.school = 1 AND 'school' LIKE :wild )
    OR (cc.chorus = 1 AND 'chorus' LIKE :wild )
    OR (cc.andris = 1 AND 'andris' LIKE :wild )
)
GROUP BY
    concerts.id
ORDER BY dt ASC