Active Record实现了这个SQL？

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 ?

我正在使用CodeIgniter。我的数据库是MySQL 5.下面的SQL语句工作正常,但我认为它与MSSQL,PG等不兼容。我想知道是否可以使用CI中的Active Record类来实现该语句,从而使其完全跨数据库?

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

我认为“GROUP_CONCAT”是我倒下的地方......

EDIT - Found on the CodeIgniter board

编辑 - 在CodeIgniter板上找到

"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."

“但是,应该注意的是,CONCAT是特定于数据库的。它实际上并不是活动记录库的一部分,尽管我确信大多数(如果不是全部)数据库引擎都支持它。”

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

我可能不得不重新考虑这一点,因为它看起来不可能使用纯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

2 个解决方案

#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?

你肯定不得不重新思考是的...为什么在所有神圣的名义中你滥用group_concat从SQL内部破坏某些内容?如果你在场上有一个“角色”,会发生什么?

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.

经验法则是:使用数据库进行存储使用PHP获取数据并将其转换为HTML或PDF,或者您想要的任何类型的输出。

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.

另外,您可能想要阅读使用JOINS?您现在正在运行2个查询,其中一个具有一些连接和一个精心构建的where子句就足够了。

#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.

我怀疑你会发现在数据库之外进行字符串操作既快又干净。从长远来看,这条道路将充满可维护性问题。

#1