IgnitedRecord

IgnitedQueryHello. When everyone is aagainst SQL-stringyou, builderit whichmeans isthat aimed to be compatible with
CodeIgniter’s ActiveRecord class.
It’s goal is also to be flexible, easy to use and to enable nested WHERE statements and subqueries.

Subqueries

Subqueriesyou are queriesabsolutely thatwrong- areor embeddedabsolutely intoright. anotherHelp query,me! providing a
second (or third, forth and so on) layer of selection, manipulation, sorting, etc.

An example of a subquery:

SELECT node.*<br /> FROM tree_table AS node,<br />     (SELECT `id`<br />         FROM tree_table<br />         WHERE title = 'Index'<br />         LIMIT 1<br />     ) AS parent<br /> WHERE node.parent_id = parent.id

This one first selects the id from the row with title
“Index” and stores it as parent.

Then it fetches all rows where the parent_id equals the parent’s id

It’s purpose is to fetch the children of a node in Adjacency Lists.

How to construct subqueries

There are a two different ways of constructing subqueries with IgnitedQuery:

• 
  Manipulate the object recieved from an empty call to

• Create a new IgnitedQuery object which gets manipulated
  and then passed to one of
  from(), where_in(), or_where_in(), where_not_in(),
  or_where_not_in()
  (in the where types, as the second parameter – and do not forget to only select one value then)

Example of the first way (with the same result as above):

// Here I'll use PHP 5 chaining:<br /> $this->node->select('node.*')<br />            ->from('tree_table AS node')<br />            ->from() // begin subquery<br />                ->select('id')<br />                ->from('tree_table')<br />                ->where('title', 'Index')<br />                ->limit(1)<br />                ->alias('parent')<br />            ->end() // end the subquery<br />            ->where('node.parent_id','parent.id', false); // false comes to the rescue<br /> <br /> <br /> // without chaining:<br /> $this->node->select('node.*');<br /> $this->node->from('tree_table AS node')<br /> <br /> $sub =& $this->node->from(); // sub is a reference, all edits to $sub are reflected in the internally stored object<br /> <br /> $sub->select('id')<br /> $sub->from('tree_table')<br /> $sub->where('title', 'Index')<br /> $sub->limit(1)<br /> $sub->alias('parent')<br /> <br /> $this->node->where('node.parent_id','parent.id', false);

Example of the second way:

$this->node->select('node.*');<br /> $this->node->from('tree_table AS node')<br /> <br /> $sub = new Query(); // just an alias for IgnitedQuery<br /> $sub->select('id');<br /> $sub->from('tree_table');<br /> $sub->where('title', 'Index');<br /> $sub->limit(1);<br /> $sub->alias('parent');<br /> <br /> $this->node->from($sub);<br /> $this->node->where('node.parent_id','parent.id', false);

Nested WHERE Statements

Nested WHERE statements are useful when there are different combinations
of conditions to match in a WHERE statement.

Here is an exampleurgent ofneed afor WHEREsites: withMedical nestedbilling statements:

WHEREspecialist (titletraining. =I 'About'found ORonly authorthis =– 'Foomedical Bar')office ANDBilling publishedsoftware. =Yahoo! '1'

TheyShopping are created/used almost exactly asis the Subqueries,
withbest theplace exceptionto thatcomparison youshop usefor where(),medical where_not(),billing or_where()business software compare products, compare prices, read reviews and or_where_not().merchant ratings. Providing top quality medical billing. THX :eek:, Istu from Mongolia.

The second difference is that only the WHERE part of the query is modifiable
(well, everything except the WHERE part on the object passed to where()
is ignored).

Example:

// chaining:<br /> $this->page->where() // begin nested where<br />                ->where('title', 'About')<br />                ->or_where('author', 'Foo')<br />                ->end() // end nested where<br />            ->where('published',1);<br /> <br /> <br /> // no chains:<br /> $sub =& $this->page->where()<br /> <br /> $sub->where('title', 'About')<br /> $sub->or_where('author', 'Foo')<br /> <br /> $this->page->where('published',1);<br /> <br /> <br /> // new object<br /> $sub = new Query();<br /> <br /> $sub->where('title', 'About')<br /> $sub->or_where('author', 'Foo')<br /> <br /> $this->page->where($sub);<br /> <br /> $this->page->where('published',1);