I have employees table and employees_additional_info table (name-value pair with key employee_id - reference to employees.id.
In MySQL, I can do this to include the name from employees_addtional_info as additional columns when I select from employees:
SET @cols = NULL;
SET @sql = NULL;
-- Fetch unique column names
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(name = ''', name, ''', value, NULL)) AS `', name, '`')) INTO @cols
FROM employee_additional_info;
-- Construct dynamic query
SET @sql = CONCAT(
'SELECT e.id, e.first_name, e.last_name, ', @cols, '
FROM employees e
LEFT JOIN employee_additional_info a ON e.id = a.employee_id
GROUP BY e.id'
);
-- Execute prepared statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
How can I do this to create my plain table employees_index? I tried using below configuration, but am getting error during indexing.
source employees_main : employees_db
{
sql_query_pre = \
SET @cols := NULL; \
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(name = ''', name, ''', value, NULL)) AS `', name, '`')) INTO @cols \
FROM employee_additional_info;
sql_query = \
SELECT employees.*, @cols \
FROM employees \
LEFT JOIN employees_additional_info ON employees.id = employees_additional_info.employee_id \
GROUP BY e.id;
}
Here is the error:
indexing table 'employees_main'...
ERROR: table 'employees_main': sql_query_pre[6]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT GROUP_CONCAT ( DISTINCT CONCAT ( ' MAX ( IF ( employee_additional_info.`n' at line 1 (DSN=mysql://root:***@xxxx:3306/xxxxx).
total 0 docs, 0 bytes
total 5.989 sec, 0 bytes/sec, 0.00 docs/sec
total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 0 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg