Active Record implementation of this SQL ?

Question

I am using CodeIgniter. My database is MySQL 5. The SQL statement below works fine, but I am thinking it would not really be compatible with MSSQL, PG, et al. I am wondering if it's possible to implement the statement using the Active Record class in CI, hence making it completely cross database ?

I think the "GROUP_CONCAT" is where I'll fall down...

EDIT - Found on the CodeIgniter board

"It should be noted, however, that CONCAT is database specific. It’s not actually a part of the active record library, although I’m sure it’s supported by most, if not all database engines."

I may have to rethink this, as it looks to be not possible using pure Active Record.

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

Answer 1

You definately have to rethink tis yes... Why in the name of all that is holy are you abusing group_concat to implode something into HTML from SQL? What happens if there is a " character in the field you're concatting?

Good rule of thumb is: Use your database for storage Use your PHP for fetching the data and transforming it to HTML or PDF, or whatever kind of output you want.

Also, you might want to read up on using JOINS ? You're now running 2 queries where one with some joins and a well-built where clause will suffice.

Answer 2

My suspicion is that you'll find it both faster and cleaner to do your string manipulation outside of the database. In the long term, this path is going to be fraught with maintainability issues.