Search code examples
mysqlgroup-concat

MySQL add array of names from the related table in select

I have the following database structure:

events

id
name
description

event_activities

event_id
activity_id

activities

id
name

I want to add sphinx search for events, which should include all related activities:

SELECT name, description, 
[Some SQL Magic that builds an array or string of names] as activities 
from events

So, the activities variable could be an array ['name1', 'name2'] or a string 'name1 | name2 | name3' and in case if one of the activity names is entered, it could include all the events that contain this activity into the search result.

Could someone help me figure out the [Magic] part

Solution

  • Read about the GROUP_CONCAT() function.

    SELECT e.name, e.description, GROUP_CONCAT(a.name) AS activities
FROM events AS e
LEFT OUTER JOIN event_activities AS ea ON ea.event_id = e.id
LEFT OUTER JOIN activities AS a ON ea.activity_id = a.id
GROUP BY e.id