Active record problem - Printable Version

Active record problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Active record problem (/showthread.php?tid=60834)

Active record problem - El Forum - 07-09-2014

[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

Active record problem - El Forum - 07-10-2014

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

Active record problem - El Forum - 07-12-2014

[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