Currently utilizing CodeIgniter with MySQL 5 for my database. The SQL query below is working fine, but I am concerned about its compatibility with MSSQL, PG, and other databases. Is it possible to convert this statement into something that can work across all databases using the Active Record class in CI?
I believe the use of "GROUP_CONCAT" might be a stumbling block...
EDIT - Discovered on the CodeIgniter forum
"It should be noted, however, that CONCAT is specific to the database. It's not actually part of the active record library, although it's likely supported by most, if not all, database engines."
I may need to reconsider this, as it seems like achieving this solely through Active Record might not be feasible.
SELECT system.system_id,
system.uuid,
system.hostname,
system.man_description,
system.man_ip_address,
system.os_short_name,
system.os_full_name,
system.man_type,
system.man_icon,
GROUP_CONCAT(DISTINCT '<a href="', oa_group.group_id, '">', oa_group.group_description, '</a>' ORDER BY group_description SEPARATOR ', ') as tag
FROM system,
oa_group,
oa_group_sys
WHERE system.system_id IN (
SELECT system.system_id
FROM system,
oa_group_sys,
oa_group,
oa_group_user
WHERE system.man_status = 'production' AND
system.system_id = oa_group_sys.system_id AND
oa_group_sys.group_id = oa_group.group_id AND
oa_group.group_id = oa_group_user.group_id AND
oa_group_user.user_id = '1' ) AND
system.system_id = oa_group_sys.system_id AND
oa_group_sys.group_id = oa_group.group_id
GROUP BY system.system_id