Fetching data from multiple tables


One of the more powerful features of Outlast Framework is its ability to automate relationships between various objects. A relationship is a connection between two objects. For example, a Band might have many fans, all of whom are actually User objects. OFW makes this really easy and automatic – you typically don’t have to write joins or worry about relationship tables.

How to create relationships?

Database theory includes one-to-one, one-to-many, many-to-one, and many-to-many relationships – all of these can be easily managed with the framework.

To define a relationship, all we need to do is to add the appropriate field to the model by placing it in our __model() definition method.

$fields->fans = zajDb::manytomany('User');

After running a database update (http://www.example.com/update/), our relationship is ready to use! If you check the database, you’ll notice that a relationship table has also been created.

Along with manytomany, you can also use manytoone, onetomany, and onetoone in your definition.

Fetching objects related to another object

Perhaps the most powerful aspect of the zajFetcher class is its ability to simplify relational JOIN queries. Basically we don’t even have to write any SQL most of the time (although you can if you want to!).

To add to our Band class, let’s assume we also have a User class, where each user has a name and nothing else. Now let’s say we want each User to be able to choose a single Band as their all-time favorite. So we must define a many-to-one relationship between the two classes. Here is the definition of User and Band respectively:

// User __model() definition
$f->name = zajDb::name();
$f->myfavorite = zajDb::manytoone('Band');
// Band __model() definition
$f->name = zajDb::name();
$f->genre = zajDb::text();
$f->biography = zajDb::textarea();
$f->bigfans = zajDb::onetomany('User', 'myfavorite');

In this example, each user can have exactly one favorite band (manytoone) and each band can have many fans (onetomany). Once we have this definition it is now very easy to access the members of the relationship, whether in PHP or within the template system. Here are examples of both:

From the other direction it looks very similar, but the result of manytoone fields will be a single object rather than a list like for manytomany or onetomany:

Complex JOINs without headaches

The really powerful stuff comes when you use fetcher classes in combination with one another. Let’s say we want all the users who have metal bands as their all-time favorite. All we need is simple PHP code:

$metal_bands = Band::fetch()->filter('genre', 'Metal');
$metal_band_fans = User::fetch()->filter('myfavorite', $metal_bands);

This is a simple example, but of course this can be expanded to really complex queries – all of which are greatly simplified via the fetcher class.

Completely custom SQL

You can also modify fetch and filter methods to produce customized results sets. This is more than needs to be covered in this introduction, but if you’re interested read more about it here.

Outlast Web & Mobile Development (c) 2018 | Privacy Policy |