translating one query in Active record CI to ordinary sql

[eluser]alvaroeesti[/eluser]
Hello,

I have been trying for many hours this issue. I have here below a working sql code, and below that the active record one. The problem that I am having is in translating the Loop, so that it renders this syntax

Code:

AND (`x`.`id` =  '37' OR `x`.`id` =  '31' OR `x`.`id` =  '36')

being x the city table.
The loop is necessary because it processes a multiple select list of cities chosen

So the sql that works is here

Code:

SELECT `asset_id`, `region_name`, `city_name`, `fname`, `lname`, `address`,

`builtyear`, `price`, `m2`, `description`, `type_id`, `city_fk`,

`transaction_type_fk`, `thumbnail`

FROM (`asset` a)
JOIN `region` y ON `y`.`id` = `a`.`region_fk`
JOIN `city` x ON `x`.`id` = `a`.`city_fk`
JOIN `seller` s ON `s`.`seller_id` = `a`.`seller_fk`
JOIN `object_type` ot ON `ot`.`type_id` = `a`.`object_type_fk`
JOIN `duration` du ON `du`.`duration_id` = `a`.`duration_fk`
JOIN `transaction_type` tt ON `tt`.`transtype_id` = `a`.`transaction_type_fk`

WHERE `y`.`id` =  '6'
AND (`x`.`id` =  '37' OR `x`.`id` =  '31' OR `x`.`id` =  '36')
AND `ot`.`type_description` = 'bed'
AND `tt`.`transtypa_desc` =  'rent'  
AND `du`.`duration_desc` =  'days'
AND `a`.`sold_by` =  'agent'
AND `a`.`firsthand` =  'y'
AND `price` >= '2'
AND `price` <= '6666'
AND `m2` >= '40'
AND `m2` <= '80'
AND `rooms` >= '2'
AND `rooms` <= '5'
AND `furniture` =  ''
AND `garage` =  ''
AND `lift` =  ''
AND `pets` =  ''
LIMIT 5

And here is the active record. Like I said, the problem seems to be the loop or how it affects the rest of the code beyond that.

Code:

$this->db->select('asset_id, region_name, region_latin, city_name, city_latin, fname, lname, address, builtyear, price, m2, description, thumbnail');
  
  $this->db->from('asset a');
  $this->db->join('region y', 'y.id = a.region_fk');
  
  $this->db->join('city x', 'x.id = a.city_fk');
  $this->db->join('seller s', 's.seller_id = a.seller_fk');
  $this->db->join('object_type ot', 'ot.type_id = a.object_type_fk');
                $this->db->join('duration du', 'du.duration_id = a.duration_fk');
  $this->db->join('transaction_type tt', 'tt.transtype_id = a.transaction_type_fk');
                
                $this->db->where('y.id', $regions);
                
                $i = 0;
                
                $nc = sizeof($cities);
                
                if ($nc > 1)

                
                {
                    
                     $this->db->where('x.id', $cities[$i]);  
                    
                    for($i = 1; $i <sizeof($cities); $i++)
                    {
                      $this->db->or_where('x.id', $cities[$i]);
                    }    
                  
                }
                
                else {
                    $this->db->where('x.id', $cities[$i]);
                     }
                
                            
              
                $this->db->where('ot.type_description', $object);
                $this->db->where('tt.transtypa_desc', $transaction_type);
                $this->db->where('du.duration_desc', $term);
  $this->db->where('a.sold_by', $soldby);
  
  $this->db->where('a.firsthand', $firsthand);
                $this->db->where('price >=', $pricefrom);
                $this->db->where('price <=', $priceto);

The problem is definitively in the loop.

I need to equal it like I said, to this sql rendering

Code:

AND (`x`.`id` =  '37' OR `x`.`id` =  '31' OR `x`.`id` =  '36')

and what it renders instead is

Code:

AND `x`.`id` =  '31'
OR `x`.`id` =  '36'
OR `x`.`id` =  '37'

which is not quite the same, because of the parentheses etc

[eluser]pickupman[/eluser]
You can pass a WHERE statement as a string. Using implode to combine your cities into a string.

Code:

$city_where = implode("' OR x.id = '", $citites); // create string from array
$city_where = ltrim($city_where, "' OR"); // trim first OR from the implode
$this->db->where('(' . $city_where .')');

Probably need to tweak a bit, but this should get you close.

[eluser]alvaroeesti[/eluser]
Thank you very much,

taking it from where you indicated, I came up with this code, (yes I know, I made it look ugly but it works)

Code:

$city_where = "AND (x.id =";
                    
                    $city_where .= implode("' OR  x.id = '", $cities); // create string from array
                    $mas = "";
                      $city_where =  ltrim($city_where, "' OR"); // trim first OR from the implode
                      $mas .= "$city_where)";
                      echo $mas;

It echoes exactly as it should. If you have a cleaner way, let me know.

Thank you very much, you solved it all.


regards