Active record problem

[eluser]Wathfea[/eluser]
Hi All,

I would like to ask someone who understand the active record better than me how could I solve this problem?

I have an SQL statement like this:

Code:

SELECT t.typeID, t.typeName, t.jitaAvgPrice, g.groupName, tp.ticketPrice, t.active
                FROM invGroups AS g
                INNER JOIN invTypes AS t ON g.groupID = t.groupID
                INNER JOIN ticketPrice AS tp ON g.groupName = tp.shipClass
                LEFT OUTER JOIN invMarketGroups AS m ON t.marketGroupID = m.marketGroupID
                WHERE g.categoryID =7
                AND t.published =1 AND (t.typeName LIKE '%Navy%' OR t.typeName LIKE '%Fleet%')
                ORDER BY t.typeName ASC

And I would like to implement this to AR:

Code:

$this->db->select('t.typeID, t.typeName, t.jitaAvgPrice, g.groupName, tp.ticketPrice, t.active');
        $this->db->from('invGroups AS g');
        $this->db->join('invTypes AS t', 'g.groupID = t.groupID', 'inner');
        $this->db->join('ticketPrice AS tp', 'g.groupName = tp.shipClass', 'inner');
        $this->db->join('invMarketGroups AS m', 't.marketGroupID = m.marketGroupID', 'left outer');
        $where = array(
            'g.categoryID' => 7,
            't.published' => 1
        );
        $this->db->like('t.typeName', 'Navy');
        $this->db->or_like('t.typeName', 'Fleet');
        $this->db->where($where);
        $this->db->order_by('t.typeName', 'asc');

But I got a different result.
The normal SQL working well and use the ( ) in the WHERE section.
WHERE g.categoryID =7 AND t.published =1 AND (t.typeName LIKE '%Navy%' OR t.typeName LIKE '%Fleet%' )
but the active record doesn't. And thats why I got few other results in my final query.

Someone can help me out?

Thx a lot, David

[eluser]joergy[/eluser]
Active record is for very simple queries. It has problems parsing expressions like "(t.typeName LIKE '%Navy%' OR t.typeName LIKE '%Fleet%')"

Use $this->db->last_query(); to look what CI makes out of Your statement.

[eluser]Wathfea[/eluser]
[quote author="joergy" date="1405023094"]Active record is for very simple queries. It has problems parsing expressions like "(t.typeName LIKE '%Navy%' OR t.typeName LIKE '%Fleet%')"

Use $this->db->last_query(); to look what CI makes out of Your statement.[/quote]

Hello!

Thx for your respon, the problem is what I tried to wrote down before, The active record miss the () out from the SQL.
This is the result of the last query:

Code:

SELECT t.typeID, t.typeName, t.jitaAvgPrice, g.groupName, tp.ticketPrice, t.active
                FROM invGroups AS g
                INNER JOIN invTypes AS t ON g.groupID = t.groupID
                INNER JOIN ticketPrice AS tp ON g.groupName = tp.shipClass
                LEFT OUTER JOIN invMarketGroups AS m ON t.marketGroupID = m.marketGroupID
                WHERE g.categoryID =7
                AND t.published =1 AND t.typeName LIKE '%Navy%' OR t.typeName LIKE '%Fleet%'
                ORDER BY t.typeName ASC