• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[Active Record] Selecting data problem

#1
[eluser]Clooner[/eluser]
My goal is te generate this query using ActiveRecord
Code:
SELECT id
FROM (categories)
WHERE categories.id NOT IN (SELECT child from category_relations)

I am attempting this by using this code
Code:
$this->db->select("id");
$sql = "SELECT child from category_relations";
$this->db->where_not_in('categories.id',$sql);
$query = $this->db->get('categories');
if ($query->num_rows()>0)
{
    return $query->result_array();
} else
    return false;

But Activerecords messes it up and generates this
Code:
SELECT `id`
FROM (`categories`)
WHERE `categories`.`id` NOT IN ('SELECT child from category_relations')

Which is an entirely different query since it adds the punctuation to the select query. This makes mysql see this a a string and not an query.

I have searched the user guide on how to disable punctuation generation(it rhymes) but didn't find anything there.

How would I have to use ActiveRecord generate the top query?

#2
[eluser]Armchair Samurai[/eluser]
This should work but hasn't been tested:
Code:
$sql = '(SELECT child FROM category_relations)';

$this->db->select('id');
$this->db->where('categories.id NOT IN', $sql, FALSE);
$query = $this->db->get('categories');

#3
[eluser]Clooner[/eluser]
[quote author="Armchair Samurai" date="1232289371"]This should work but hasn't been tested:[/quote] This code works, thanks a lot.

But I wonder isn't there a complete ActiveRecord way of doing this?

#4
[eluser]Clooner[/eluser]
Again I ran into the same problem with Active records
Code:
$this->db->select("highlights.*");
$this->db->select("highlight_meta.highlight_id");
$this->db->select("highlight_meta.key");
$this->db->select("highlight_meta.value");
$this->db->where("highlights.title",$title);
$this->db->join('highlight_meta', 'highlight_meta.highlight_id = highlights.id','left');
$query = $this->db->get('highlights');
if ($query->num_rows()>0)
    return $this->meta_result_array($query->result_array(),'highlight_id');
else
    return false;
AR also auto escapes the select statement. Which breaks up

Code:
SELECT highlights. * ,
and turns it into this
Code:
SELECT `highlights`.`*`,
which does not work in MySQL

How should I do this query using Active records or maybe even better: Turn off the auto escaping of some parts of my query Big Grin

#5
[eluser]Clooner[/eluser]
I found in the user guide that if I change

Code:
$this->db->select("highlights.*");

into

Code:
$this->db->select("highlights.*",false);

Active records will not produce the backticks in the select statement.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.