PEAR Handbuch
ZurückNach vorne

Advanced Filters

Advanced Filters --  Advanced Filters - ::query(), ::SelectAdd(), ::whereAdd(), ::Limit(), ::OrderBy(), ::GroupBy(),

More advanced Querying

The basic features allow most simple queries to be done very quickly, however building more complex queries can be done using the methods listed below, which append or set conditions that build the query. You will find that there is a fine balance between using these builder methods and just using raw SQL.

The DB_DataObject class does not handle JOIN queries directly for the simple reason that it is designed to simplify the use of databases, and make the code that access them clear and easy to read. Having experimented with a Join Version, it became very clear that this did not fulfill either of these goals, and made the resulting code more difficult to read, debug and understand. So in reality it is best to use the raw query() method to do joined queries..

::query()

Raw Database Queries

::query (string query)

Sends a raw Query to the database

Beispiel 1. raw Queries on the database 

$person = new DataObjects_Person;
$person->query("select * from {$person->__table} where id > 12");
while ($person->fetch()) {
  echo $person->name;
}

::selectAdd()

Adding and removing Items from select query

::select ([string Select Items])

By default a select query will request all items '*', to change this behavior you can first call selectAdd() without any arguments to clear the current request and then add the specific items you require.

Beispiel 2. Adding select statements

$person = new DataObjects_Person;
$person->selectAdd();
$person->selectAdd('id,name');
while ($person->fetch()) {
  echo "$person->id} {$person->name}<BR>";
}
$person = new DataObjects_Person;
$person->selectAdd("DATE_FORMAT(birthday,'%d %m %Y') as birthday_formated ");
$person->id = 12;
$person->find(TRUE);
echo "$person->name} {$person->birthday_formated}<BR>";
}

This would execute the following SQL statements,

Beispiel 3. Resulting SQL 

SELECT id,name FROM person
SELECT *, DATE_FORMAT(birthday,'%d %m %Y') as birthday_formated  FROM person WHERE id=12

You can also set up a default selection query by adding SelectAdd() method calls in the object initialization method (the one with the same name as the class)

::whereAdd()

Adds conditions to the Where part of the query

::whereAdd ([string Where [, string OR]])

Adds items to the where part of a SQL query. Calling this without any arguments clears, the where condition. The default behavior is to add 'AND' queries, send the string 'OR' to append OR conditions.

Beispiel 4. Adding Where Statements

$person = new DataObjects_Person;
$person->whereAdd('age > 12');
$person->whereAdd('age < 30');
$person->find();

while ($person->fetch()) {
    echo "$person->id} {$person->name}<BR>";
}
$person = new DataObjects_Person;
$person->whereAdd('age < 12');
$person->whereAdd('age > 30', 'OR');
$person->find();

while ($person->fetch()) {
    echo "$person->id} {$person->name}<BR>";
}

This would execute the following SQL statements,

Beispiel 5. Resulting SQL 

SELECT * FROM person WHERE age > 12 AND age < 30

SELECT * FROM person WHERE age < 12 OR age > 30

::limit()

Sets the limit of a query

::limit ([int (Limit Range Start or Number) [, int (Limit Range Number)]])

Sets the limit range. Calling this without any arguments clears the range

Beispiel 6. Setting the Limit

$person = new DataObjects_Person;
$person->limit(2);
$person->find();
while ($person->fetch()) {
    echo "$person->id} {$person->name}<BR>";
}


$person = new DataObjects_Person;
$person->limit(2,4);
$person->find();

while ($person->fetch()) {
    echo "$person->id} {$person->name}<BR>";
}

This would execute the following SQL statements,

Beispiel 7. Resulting SQL 

SELECT * FROM person LIMIT 2

SELECT * FROM person LIMIT 2,4

::orderBy()

Appends to the 'order by' part of the query

::orderBy ([string Order])

Appends an Order condition. Calling this without any arguments clears the order

Beispiel 8. setting the order by

$person = new DataObjects_Person;
$person->orderBy('name');
$person->orderBy('age, eye');
$person->find();

This would execute the following SQL statement,

Beispiel 9. Resulting SQL 

SELECT * FROM person ORDER BY name, age, eye

::groupBy()

Appends a to the 'group by' part of the query

::groupBy ([string Group])

Appends a Group condition. Calling this without any arguments clears the Group Condition

Beispiel 10. Setting the Group by 

$person = new DataObjects_Person;
$person->groupBy('name');
$person->groupBy('age, eye');
$person->find();

This would execute the following SQL statement,

Beispiel 11. Resulting SQL 

SELECT * FROM person GROUP BY name, age, eye

ZurückZum AnfangNach vorne
Modifying the databaseNach obenAutomatic Table Linking