active record // where() or_where_in() question

[eluser]jurlan[/eluser]
Hi all,

It might be a stupid question but how do I put the following part of a query :

Code:

WHERE((i.Nationality=2) OR (i.ID IN (4356, 77071))

in the active record form with

Code:

$this->db->where('i.Nationality', 2)->or_where_in('i.ID', $numbers);

where $numbers is predefined array containing the 2 numbers.

My example doesn't produce the outer () .. is there a way to do it?

Thanks a lot
/jurlan

[eluser]Mr. Pickle[/eluser]
It would be useful to know how the total query looks like.
Is the where and the where_in the only selecting you do?

Can you post your code? And also the output of

Code:

$this->db->last_query();

from immediately after the query fired?
Then it's visible how the actual query that ran was built up.

[eluser]jurlan[/eluser]
this is the original query:

Code:

$sql  = "SELECT DISTINCT c.ID AS CarrierID, c.Title, c.VersionInfo, im.Filename FROM carriers c ";
$sql .= "USE INDEX (PRIMARY) ";
$sql .= "INNER JOIN carriermainartists cm ON cm.CarrierID=c.ID ";
$sql .= "INNER JOIN identities i ON i.ID=cm.IdentityID ";
$sql .= "INNER JOIN dblinking li ON li.ObjectID=c.ID AND li.ObjectType=5 AND li.LinkType=9 AND li.LinkCategory=25 ";
$sql .= "INNER JOIN images im ON im.ID=li.LinkID ";
$sql .= "WHERE((i.Nationality=2) OR (i.ID IN (4356, 77071)) OR (c.Concept=4)) AND (c.Hidden = 0) AND ((c.Filter & 1)=1) ";
$sql .= "AND c.ID='.$randCarrierID.'  LIMIT 1";

this is what I've written so far:

Code:

$this->db->distinct();
$this->db->select('c.ID, c.Title, c.VersionInfo, im.Filename');
$this->db->join('carriermainartists cm', 'cm.CarrierID=c.ID', 'inner');
$this->db->join('identities i', 'i.ID=cm.IdentityID', 'inner');
$this->db->join('dblinking li', 'li.ObjectID=c.ID AND li.ObjectType=5 AND li.LinkType=9 AND li.LinkCategory=25', 'inner');
$this->db->join('images im', 'im.ID=li.ID', 'inner');
$this->db->where('i.Nationality', 2)->or_where_in('i.ID', $this->numbers)->or_where('c.Concept', 4);
$this->db->where('c.Hidden', 0)->where('(c.Filter & 1) = 1')->where('c.ID', $randCarrierID);
$this->db->limit(1);

$result = $this->db->get('carriers c');

$data = $result->result();

I'm also wondering if there a way to get rid of the AND's in the dblinking join

And this is the output of my $this->db->last_query();

Code:

SELECT DISTINCT `c`.`ID`, `c`.`Title`, `c`.`VersionInfo`, `im`.`Filename`
FROM (`carriers` c) INNER JOIN `carriermainartists` cm ON `cm`.`CarrierID`=`c`.`ID`
INNER JOIN `identities` i ON `i`.`ID`=`cm`.`IdentityID`
INNER JOIN `dblinking` li ON `li`.`ObjectID`=`c`.`ID` AND li.ObjectType=5 AND li.LinkType=9 AND li.LinkCategory=25
INNER JOIN `images` im ON `im`.`ID`=`li`.`ID`
WHERE `i`.`Nationality` = 2 OR `i`.`ID` IN (4356, 77071) OR `c`.`Concept` = 4 AND `c`.`Hidden` = 0 AND (c.Filter & 1) = 1
AND `c`.`ID` = 18806 LIMIT 1

Thanks!

[eluser]Aken[/eluser]
There's no way to create that with active record right now. If you want to group WHERE conditions, you need to just use one WHERE statement for the entire group, meaning you'll still be writing some of it by hand.

There is some suggested code (a pull request) to the Github repository for providing group support in the query builder for WHERE statements. Hard to say if it'll be included soon, since it would need to be accepted for every type of database driver. But it might be something you can incorporate into your application somehow.

[eluser]jurlan[/eluser]
OK .. thanks a lot!