Active Record Subqueries

[eluser]NTICompass[/eluser]
I couldn't create a wiki page, so I'll just post this here. I made a subquery library for active records. I hope people find this useful.

EDIT: I was able to post to the wiki (http://codeigniter.com/wiki/Subqueries/).

It lets you use active record methods to create subqueries in SQL queries.
It supports SELECT, JOIN, FROM (and other statements, I guess). It also supports subqueries inside subqueries.

Example 1 (SELECT):

Code:

SELECT `word`, (SELECT `number` FROM (`numbers`) WHERE `numberID` = 2) AS number FROM (`words`) WHERE `wordID` = 3

Code:

$this->db->select('word')->from('words')->where('wordID', 3);
$sub = $this->subquery->start_subquery('select');
$sub->select('number')->from('numbers')->where('numberID', 2);
$this->subquery->end_subquery('number');

Example 2 (FROM):

Code:

SELECT `test`, `test2` FROM ((SELECT 3 AS test) AS testing, (SELECT 4 AS test2) AS testing2)

Code:

$this->db->select('test');
$sub = $this->subquery->start_subquery('from');
$sub->select('3 AS test', false);
$this->subquery->end_subquery('testing');
$this->db->select('test2');
$sub = $this->subquery->start_subquery('from');
$sub->select('4 AS test2', false);
$this->subquery->end_subquery('testing2');

Methods:

start_subquery($statement, $join_type, $join_on) - Opens a subquery, and returns a DB object. Call all active record methods on this object.
Parameters:
$statement - SQL statement to put subquery into (select, from, join, etc.)
$join_type - JOIN type (only for join statements)
$join_on - JOIN ON clause (only for join statements)
Return:
database object

end_subquery($alias) - Closes a subquery.
Parameters:
$alias - Alias to use for subquery

[eluser]dfreerksen[/eluser]
Works great even in CI2. Thank you!

[eluser]NTICompass[/eluser]
[quote author="dfreerksen" date="1293762041"]Works great even in CI2. Thank you![/quote]
You're welcome. :-D

[eluser]CodeTroll[/eluser]
Works like a charm.. Had to change the is_callable check in the constructor from

Code:

$this->func = is_callable(array($this->db, '_compile_select')) ? '_compile_select' : is_callable(array($this->db, 'get_compiled_select')) ? 'get_compiled_select' : null;

to

Code:

$this->func = is_callable(array($this->db, '_compile_select')) ? '_compile_select' : (is_callable(array($this->db, 'get_compiled_select')) ? 'get_compiled_select' : null);

otherwise it kept returning get_compiled_select even when _compile_select was the one available.

[eluser]NTICompass[/eluser]
[quote author="CodeTroll" date="1346658406"]Works like a charm.. Had to change the is_callable check in the constructor from

Code:

$this->func = is_callable(array($this->db, '_compile_select')) ? '_compile_select' : is_callable(array($this->db, 'get_compiled_select')) ? 'get_compiled_select' : null;

to

Code:

$this->func = is_callable(array($this->db, '_compile_select')) ? '_compile_select' : (is_callable(array($this->db, 'get_compiled_select')) ? 'get_compiled_select' : null);

otherwise it kept returning get_compiled_select even when _compile_select was the one available.[/quote]

derp X_X

I wrote that line late one night, didn't realize I'd missed that set of parenthesis. Thanks for telling me, I'll fix that in GitHub later :-)

[eluser]CodeTroll[/eluser]
No worries - I do however have a problem - might just be lack of coffee this morning...

This was my old - presubquery query:

Code:

$this->db->distinct();
$this->db->select('first_name,last_name,id');
$this->db->where('id NOT IN (select distinct user_id as id from sb_'.$this->tables['members'].' where group_id='.$group_id.')');
$this->db->order_by('first_name');
$query = $this->db->get('users');

There I had the $query object to work with, but how do I do it with subquery???
This is what I have:

Code:

$this->db->distinct();
$this->db->order_by('first_name');
$this->db->select('first_name,last_name,id');
$this->db->from('users');
$sub = $this->subquery->start_subquery('where_not_in');
$sub->select('id');
$sub->from($this->tables['members']);
$sub->where('group_id', $group_id);
$this->subquery->end_subquery('id');

If I run echo $this->db->last_query(); I get the correct query... but how to get the result of that??

I usually end my db functions with this (or similar):

Code:

if ($query->num_rows() > 0) {
   return $query->result();
  } else {
   return FALSE;
  }

[eluser]CodeTroll[/eluser]
Or am I just being completely daft?

[eluser]NTICompass[/eluser]
[quote author="CodeTroll" date="1346660747"] No worries - I do however have a problem - might just be lack of coffee this morning...

This was my old - presubquery query:

Code:

$this->db->distinct();
$this->db->select('first_name,last_name,id');
$this->db->where('id NOT IN (select distinct user_id as id from sb_'.$this->tables['members'].' where group_id='.$group_id.')');
$this->db->order_by('first_name');
$query = $this->db->get('users');

There I had the $query object to work with, but how do I do it with subquery???
This is what I have:

Code:

$this->db->distinct();
$this->db->order_by('first_name');
$this->db->select('first_name,last_name,id');
$this->db->from('users');
$sub = $this->subquery->start_subquery('where_not_in');
$sub->select('id');
$sub->from($this->tables['members']);
$sub->where('group_id', $group_id);
$this->subquery->end_subquery('id');

If I run echo $this->db->last_query(); I get the correct query... but how to get the result of that??

I usually end my db functions with this (or similar):

Code:

if ($query->num_rows() > 0) {
   return $query->result();
  } else {
   return FALSE;
  }

[/quote]

In order to do "WHERE NOT IN", you need to call "$sub = $this->subquery->start_subquery('where_in');", and then "$this->subquery->end_subquery('id', FALSE);".

Also, you need to call "$this->db->get()" to actually run the query.

Also, you don't need to put the "order_by" before the subquery call (unless you are using "$this->subquery->start_union()").

Try something like this. This should be equivalent to your original query.

Code:

$this->db->distinct();
$this->db->select('first_name,last_name,id');
$this->db->from('users');

$sub = $this->subquery->start_subquery('where_in');
$sub->distinct()->select('id');
$sub->from('sb_'.$this->tables['members']);
$sub->where('group_id', $group_id);
$this->subquery->end_subquery('id', FALSE);

$this->db->order_by('first_name');
$query = $this->db->get();

[eluser]CodeTroll[/eluser]
Great - thank you

This statement works.. it doesn't need the ..->distinct()->select(... but rather the ->select('user_id as id..

Code:

$this->db->distinct();
$this->db->select('first_name,last_name,id');
$this->db->from('users');
$sub = $this->subquery->start_subquery('where_in');
$sub->select('user_id as id');
$sub->from($this->tables['members']);
$sub->where('group_id', $group_id);
$this->subquery->end_subquery('id',false);
$this->db->order_by('first_name');
$query = $this->db->get();

[eluser]NTICompass[/eluser]
[quote author="CodeTroll" date="1346691558"]Great - thank you

This statement works.. it doesn't need the ..->distinct()->select(... but rather the ->select('user_id as id..

Code:

$this->db->distinct();
$this->db->select('first_name,last_name,id');
$this->db->from('users');
$sub = $this->subquery->start_subquery('where_in');
$sub->select('user_id as id');
$sub->from($this->tables['members']);
$sub->where('group_id', $group_id);
$this->subquery->end_subquery('id',false);
$this->db->order_by('first_name');
$query = $this->db->get();

[/quote]

Glad I could help :-)

I only added the distinct because your 1st query had "select distinct user_id". :-P