Making queries

Once you’ve created your data models, Powerorm automatically gives you a database-abstraction API that lets you create, retrieve, update and delete objects.

This document explains how to use this API.

Throughout this guide (and in the reference), we’ll refer to the following models, which comprise a Weblog application:

// models/Blog

namespace App\Models;

use Eddmash\PowerOrm\Model\Model;

class Blog extends Model
{

    public function unboundFields()
    {
        return [
            'name'=>Model::CharField(['maxLength'=>100]),
            'tagline'=>Model::TextField()
        ];
    }
}

// models/Author

namespace App\Models;

use Eddmash\PowerOrm\Model\Model;

class Author extends Model
{


    public function unboundFields()
    {
        return [
            'name'=>Model::CharField(['maxLength'=>200]),
            'email'=>Model::EmailField()
        ];
    }
}


// models/Entry
namespace App\Models;

use Eddmash\PowerOrm\Model\Model;

/**
* Class Entry
*/
class Entry extends Model
{
    public function unboundFields()
    {
        return [
            'blog'=>Model::ForeignKey(['to'=>Blog::class]),
            'headline'=>Model::CharField(['maxLength'=>255]),
            'blog_text'=>Model::TextField(),
            'authors'=>Model::ManyToManyField(['to'=>Author::class]),
            'n_comments'=>Model::IntegerField(),
            'n_pingbacks'=>Model::IntegerField(),
            'ratings'=>Model::IntegerField(),
        ];
    }

}

Creating objects

To represent database-table data in PHP objects, Powerorm uses an intuitive system:

• A model class represents a database table, and
• an instance of that class represents a particular record in the database table.

To create an object, instantiate it using an associative array(keys are name of a model field, values are the value to assign to the fields) to the model class, then call save() to save it to the database.

Using the block model we created above :

$blog = new \App\Models\Blog();
$blog->name = "Beatles Blog";
$blog->tagline='All the latest Beatles news.';
$blog->save();

This performs an INSERT SQL statement behind the scenes. Powerorm doesn’t hit the database until you explicitly call save().

The save() method has no return value.

Saving changes to objects

To save changes to an object that’s already in the database, use save().

Given a Blog instance $blog that has already been saved to the database, this example changes its name and updates its record in the database:

$blog->name = 'New name';
$blog->save();

This performs an UPDATE SQL statement behind the scenes. Powerorm doesn’t hit the database until you explicitly call save().

Saving ForeignKey and ManyToManyField fields

Updating a ForeignKey field works exactly the same way as saving a normal field – simply assign an object of the right type to the field in question. This example updates the blog attribute of an Entry instance entry, assuming appropriate instances of Entry and Blog are already saved to the database (so we can retrieve them below):

$en = \App\Models\Entry::objects()->get(['pk' => 1]);
$en->blog = \App\Models\Blog::objects()->get(['pk'=>4]);
$en->headline = "Filtered :doc:`QuerySet <queryset>`s are unique";
$en->blog_text = "These three :doc:`QuerySet <queryset>`s are separate.";
$en->save();

Updating a ManyToManyField works a little differently – use the add() method on the field to add a record to the relation. This example adds the Author instance joe to the entry object:

$en = \App\Models\Entry::objects()->get(['pk' => 1]);
$en->authors->add(\App\Models\Author::objects()->get(['name'=>'joe']));

To add multiple records to a ManyToManyField in one go, include multiple arguments in the call to add(), like this:

$en = \App\Models\Entry::objects()->get(['pk' => 1]);

$paul = \App\Models\Author::objects()->get(['name'=>'paul']);
$john = \App\Models\Author::objects()->get(['name'=>'john']);
$george = \App\Models\Author::objects()->get(['name'=>'george']);
$joe = \App\Models\Author::objects()->get(['name'=>'joe']);
$en->authors->add($paul, $john, $george, $joe);

Note

Powerorm will complain if you try to assign or add an object of the wrong type.

Retrieving objects

To retrieve objects from your database, construct a QuerySet via a Manager on your model class.

A QuerySet represents a collection of objects from your database. It can have zero, one or many filters. Filters narrow down the query results based on the given parameters.

In SQL terms, a QuerySet equates to a SELECT statement, and a filter is a limiting clause such as WHERE or LIMIT.

You get a QuerySet by using your model’s Manager. Each model has at least one Manager, which is accessed via the static method object(). Access it directly via the model class, like so:

\App\Models\Blog::objects()

The Manager is the main source of a QuerySet for a model.

For example, App\Models\Blog->objects->all() returns a QuerySet that contains all Blog objects in the database.

Retrieving all objects

The simplest way to retrieve objects from a table is to get all of them. To do this, use the all() method on a Manager:

\App\Models\Blog::objects()->all()

The all() method returns a QuerySet of all the objects in the database.

Retrieving specific objects with filters

The QuerySet returned by all() describes all objects in the database table. Usually, though, you’ll need to select only a subset of the complete set of objects.

To create such a subset, you refine the initial QuerySet, adding filter conditions. The two most common ways to refine a QuerySet are:

filter()

Returns a new QuerySet containing objects that match the given lookup parameters.

exclude()

Returns a new QuerySet containing objects that do not match the given lookup parameters.

This method take an associative array of parameters.

For example, to get a QuerySet of Authors who have the letter ‘joe’ in there name, use filter() like so:

\App\Models\Author::objects()->filter(['name__contains'=>'joe'])

With the default manager class, it is the same as:

\App\Models\Author::objects()->all()->filter(['name__contains'=>'joe'])

Chaining filters

The result of refining a QuerySet is itself a QuerySet, so it’s possible to chain refinements together. For example:

\App\Models\Entry::objects()
    ->filter(['headline__startswith'=>'what'])
    ->exclude(['rating__lte'=>3])
    ->filter(['blog_text__contains'=>'kenya']);

This takes the initial QuerySet of all entries in the database, adds a filter, then an exclusion, then another filter. The final result is a QuerySet containing all entries with a headline that starts with “What”, excluding any entries with a rating of 3 or less and the blog_text contains the word ‘kenya’.

Filtered QuerySets are unique

Each time you refine a QuerySet, you get a brand-new QuerySet that is in no way bound to the previous QuerySet.

Each refinement creates a separate and distinct QuerySet that can be stored, used and reused.

Example:

$qs1 = \App\Models\Entry::objects()->filter(['headline__startswith' => 'what']);
$qs2 = $qs1->exclude(['rating__gte' => 3]);
$qs3 = $qs1->filter(['blog_text__contains' => 'kenya']);

These three QuerySet are separate.

• The first is a base QuerySet containing all entries that contain a headline starting with “What”.
• The second is a subset of the first, with an additional criteria that excludes any entries with a rating of 3 or less.
• The third is a subset of the first, with an additional criteria that selects only the records whose the blog_text contains the word ‘kenya’.

The initial QuerySet ($q1) is unaffected by the refinement process.

QuerySets are lazy

QuerySet are lazy – the act of creating a QuerySet doesn’t involve any database activity. You can stack filters together all day long, and Powerorm won’t actually run the query until the QuerySet is evaluated.

Take a look at this example:

$qs = \App\Models\Entry::objects()->filter(['headline__startswith' => 'what']);
$qs = $qs->exclude(['ratings' => 3]);
$qs = $qs->filter(['blog_text__contains' => 'kenya']);
var_dump($qs);

Though this looks like three database hits, in fact it hits the database only once, at the last line (var_dump($qs)). In general, the results of a QuerySet aren’t fetched from the database until you “ask” for them. When you do, the QuerySet is evaluated by accessing the database.

For more details on exactly when evaluation takes place, see When QuerySets are evaluated.

Retrieving a single object with get()

filter() will always give you a QuerySet, even if only a single object matches the query - in this case, it will be a QuerySet containing a single element.

If you know there is only one object that matches your query, you can use the get() method on a Manager which returns the object directly:

$autor = \App\Models\Author::objects()->get(['pk' => 1]);

You can use any query expression with get(), just like with filter() - again, see Field lookups below.

Note

There is a difference between using get(), and using filter() with a limit(). If there are no results that match the query, filter() will raise a DoesNotExist exception. so in the code above, if there is no Author object with a primary key of 1, Powerorm will raise DoesNotExist.

Similarly, Powerorm will complain if more than one item matches the filter() query. In this case, it will raise MultipleObjectsReturned.

Other QuerySet methods

Most of the time you’ll use all(), get(), filter() and exclude() when you need to look up objects from the database. However, that’s far from all there is; see the QuerySet API Reference for a complete list of all the various QuerySet methods.

Limiting QuerySets

To limit() your QuerySet to a certain number of results. This is the equivalent of SQL’s LIMIT and OFFSET clauses.

For example, this returns the first 5 objects (LIMIT 5):

var_dump(\App\Models\Entry::objects()->all()->limit(null,5));

This returns the sixth through tenth objects (OFFSET 5 LIMIT 5):

var_dump(\App\Models\Entry::objects()->all()->limit(5,5));

Limiting a QuerySet returns a new QuerySet.

Field lookups

Field lookups are how you specify the meat of an SQL WHERE clause. They’re specified as an associative array to the QuerySet methods filter(), exclude() and get().

Basic lookups keyword arguments take the form [field__lookuptype=> value]. (That’s a double-underscore). For example:

\App\Models\Blog::objects()->filter(['name__istartswith'=>"a"])

translates (roughly) into the following SQL:

SELECT * FROM blog  WHERE name LIKE 'a%'

The field specified in a lookup has to be the name of a model field. There’s one exception though, in case of a ForeignKey you can specify the field name suffixed with _id. In this case, the value parameter is expected to contain the raw value of the foreign model’s primary key. For example:

\App\Models\Entry::objects()->filter(['blog_id'=>1]);

Lookups that span relationships

Powerorm offers a powerful and intuitive way to “follow” relationships in lookups, taking care of the SQL JOINs for you automatically, behind the scenes. To span a relationship, just use the field name of related fields across models, separated by double underscores, until you get to the field you want.

This example retrieves all Entry objects with a Blog whose name is ‘Beatles Blog’:

\App\Models\Entry::objects()->filter(['blog__name'=>"Beatles Blog"]);

This spanning can be as deep as you’d like.

It works backwards, too. To refer to a “reverse” relationship, just use the lowercase name of the model.

This example retrieves all Blog objects which have at least one Entry whose headline contains ‘Lennon’:

More Queries Information

• Making queries
• QuerySet API reference
• Custom Lookups
• Query Expressions
• Helpers