run two queries one return

[eluser]tim1965[/eluser]
Hi

Apologies if this is a stupid question.

I want to run a numer of different queries in one function in my model. However i only want to return one dataset, combining all the results.
Similar to chaining a set of queries using transaction, but only returning one array of data containing all the resultsets.
I need to run a number of select min and maxs and then combine them with a further more complex select. I cannot get these to play together nicely.
I know i can probably do this in my controller, but thought i would check if there is a shortcut.
So is this possible, and if so how. Thanks in advance

[eluser]libnac[/eluser]
Hi,
you can use the methods you want of a model.

class MyModel extends Model
{
..
function f1() {
return "Hello";
}
function f2() {
return " World !";
}
function f3{} {
return array("result1" => $this->f1(), "result2" => $this->f2());
}


}

I hope this helps you.

[eluser]tim1965[/eluser]
Gracias Mi Amigos

I will give this a try later today.

Where abouts in Spain are you. I am sat at Malaga airport at the moment.

[eluser]libnac[/eluser]

I am at Elche (Alicante).. so far from Málaga.. around 500 kms.
have a nice trip..

[eluser]darkhouse[/eluser]
Just for clarification, you don't need to create other functions to do what you're trying to do. However, the benefit for having separate functions is that you can call those functions individually, should you need just the result of one of them at some point. But if you don't need them separate, you can just do this:

Code:

class My_model extends Model {

   function My_model(){
      parent::Model();
   }

   function get_some_data(){
      $data = array("Hello", " World!");
      return $data;
   }

}

Doesn't make much difference. Depends if you need those functions individually for other purposes or not.

One thing I do often is grab a result from the database, then grab another result and attach it to the first results, like this:

Code:

class My_model extends Model {

   function My_model(){
      parent::Model();
   }

   function get_some_data(){
      $query = $this->db->get('some_table');
      $result = $query->result();
      foreach($result as &$res){ //note the & on $res, this means it's a reference
         $this->db->where('res_id', $res->id);
         $query = $this->db->get('some_other_table');
         $res->some_array = $query->result();
      }
      return $result;
   }

}

However, I only do this when a join isn't sufficient, and I'm not getting that many records. I'm pulling say 10 records from the initial query, it ends up being 11 queries in total, which isn't terrible. I don't suggest it if you're pulling a lot of records in the initial query.

[eluser]tim1965[/eluser]
Thanks for the replies

My problem is i already have a large and complex search query using active records that uses a lot of joins already. I wanted to add another table and run a $this->db->select_min and max on it. But the query is failing when i use this as it doesnt like the join syntax for the table when i specify the field. I will try both options(assuming the plane ever takes off), and let you know. I suspect the last option will be closer to what i am trying to achieve. thanks again.

[eluser]kurucu[/eluser]
If this is just to simplify the SQL statement, then perhaps post that and let us take a look. A single optimised SQL statement would be quicker and cleaner in a single call if that's what you're trying to achieve.

[eluser]tim1965[/eluser]
Hi

Ok i will start from the beginning.
I have a query that works correctly. I am trying to get a min and max back from anadditional table. So i ahve added the select_max at the front and a join (the last one for "V_charges"
The new query is

Code:

$this->db->select_max('v_charges_date_price.price_per_week_1');
$this->db->select('master_property_reference.property_id, master_property_details.provinceorcounty, master_property_details.contact_country, master_property_details.region, master_property_details.property_sleeps, master_property_details.townorcity, master_property_details.property_name, master_property_details.property_highlights, master_property_details.property_type, master_property_details.floorspace, master_property_details.no_of_bedrooms, master_property_details.no_of_bathrooms, master_pictures.thumbnail, master_charges.currency, master_contactdetails.adverttype');$this->db->from('master_property_reference');
$this->db->join('master_property_details', 'master_property_reference.property_id =master_property_details.property_id');
$this->db->join('master_pictures', 'master_property_reference.property_id = master_pictures.property_id AND master_pictures.photo_desc = "main"');
$this->db->join('master_charges', 'master_property_reference.property_id = master_charges.property_id');
$this->db->join('master_contactdetails', 'master_property_reference.property_id = master_contactdetails.property_id');
$this->db->join('v_charges_date_price', 'master_property_reference.property_id = v_charges_date_price.property_id');
$this->db->where('ad_completed',$t);
$this->db->like('master_property_details.contact_country',$country);
$this->db->like('master_property_details.region',$region);
$this->db->where('master_property_details.property_sleeps <=',$property_sleeps);
$this->db->where('master_property_details.no_of_bedrooms >=',$no_of_bedrooms);
$this->db->like('master_property_details.complex_type',$complex_type);
$this->db->like('master_property_details.broadband',$broadband);
$this->db->like('master_property_details.cable',$cable);
$this->db->like('master_property_details.outside_pool',$outside_pool);
$this->db->like('master_property_details.indoor_pool',$indoor_pool);
$this->db->like('master_property_details.shared_pool',$communal_pool);
$this->db->like('master_property_details.jacuzzi_or_hot_tub',$jacuzzi);
$this->db->like('master_property_details.cot',$cot);
$this->db->like('master_property_details.suitability_young_children',$small_children);
$this->db->like('master_property_details.suitability_wheelchair',$wheelchairs);
$this->db->like('master_property_details.suitability_pets_allowed',$pets);
$this->db->like('master_property_details.canoeing', $canoeing);
$this->db->like('master_property_details.cycling', $cycling);
$this->db->like('master_property_details.golf', $golf);
$this->db->like('master_property_details.horseriding', $horse_riding);
$this->db->like('master_property_details.Mountaineering', $Mountaineering);
$this->db->like('master_property_details.para_gliding', $paragliding);
$this->db->like('master_property_details.sailing', $sailing);
$this->db->like('master_property_details.scuba_diving', $scuba_diving);
$this->db->like('master_property_details.sea_fishing', $sea_fishing);
$this->db->like('master_property_details.snow_skiing', $skiing);
$this->db->like('master_property_details.surfing', $surfing);
$this->db->like('master_property_details.tennis', $tennis);
$this->db->like('master_property_details.walking', $walking);
$this->db->like('master_property_details.wind_surfing', $wind_surfing);
$this->db->like('master_property_details.young_children', $young_children);
$this->db->like('master_property_details.teenagers', $teenagers);
$this->db->like('master_property_details.eighteen', $eighteen);
$this->db->like('master_property_details.romantic_couples', $romantic_couple);
$this->db->like('master_property_details.lively', $lively);
$this->db->like('master_property_details.relaxing', $relaxing);
$this->db->like('master_property_details.picturesque', $picturesque);
$this->db->like('master_property_details.romantic', $romantic);
$this->db->like('master_property_details.historic', $historic);
$this->db->like('master_property_details.spa', $spa);
$this->db->like('master_property_details.sophisticated', $sophisticated);
$this->db->like('master_property_details.activity', $activity);
$this->db->like('master_property_details.inexpensive', $inexpensive);
$this->db->like('master_property_details.by_the_beach', $by_the_beach);
$this->db->like('master_property_details.near_the_coast', $near_the_coast);
$this->db->like('master_property_details.resort', $resort);
$this->db->like('master_property_details.lakeside', $lakeside);
$this->db->like('master_property_details.mountainous', $mountainous);
$this->db->like('master_property_details.rural', $rural);
$this->db->like('master_property_details.village', $village);
$this->db->like('master_property_details.citycentre', $city_centre);
$this->db->like('master_property_details.waterfront', $waterfront);
$this->db->limit($limit,$offset);
$query = $this->db->get();

if($query->num_rows() >0)
{
$row=$query->result_array();
return $row;
}    
}

If i run this the SQL bombs out because the active record select_max tries to do this to the column name (relevant part of query)

SELECT `MAX(`v_charges_date_price`.`price_per_week_1`)` AS price_per_week_1,

it should look like this (note ' around MAX and the join and column name)

SELECT MAX(v_charges_date_price.price_per_week_1) AS price_per_week_1,

This is a limitation of active record as it doesnt handle JOIN well i think.
Any help appreciated

[eluser]tim1965[/eluser]
[CONTINUED]
Trying your approach

I can only seem to get the result of the second query back i.e. max value 300. I ran the above query minus selct_max and the associated join. Where the first db get is at the end of the above query

Code:

$query = $this->db->get();

if($query->num_rows() >0)
        {
        $row=$query->result_array();

foreach($row as &$key){ //note the & on $res, this means it's a reference
         $this->db->where('property_id', $key->property_id);
         $this->db->select_max('price_per_week_1');
         $query = $this->db->get('v_charges_date_price');
         $key->$t = $query->result_array();
      }
      return $t;

I want the value of the second query appended to the result of the first query.
Thanks

[eluser]tim1965[/eluser]
Can anybody else help me on this ??