Loading

We can already define, save and delete an entity. That leaves us with the most interesting part - loading.

Data loading is a more frequent activity in a common online application and it is a very critical point of the application, which is very often the cause of slowness and performance problems in general. That's why loading in Jet DataModel is a bit more involved and there are several ways to load data. It is important to know, understand and most importantly use them correctly (and verify everything via profiler).

Let's get familiar with all the data loading options and all the specifics.

Loading one specific object - method load::( )

This is a basic situation where you need to get an instance of a specific object - a specific entity - based on some conditions. This is what the static load method is for. The basic usage is quite simple. Again, let's use a sample application as an example. You have an article ID and you want an instance of it:

If there is an article that has the given ID, the $article variable will contain its instance, otherwise the value will be NULL.

But that's not nearly enough. As you know from the chapter on ID controllers, a record can be identified by multiple properties.

Let's go over all the options now.

Loading using a simple ID

If the ID is made of one property and we want to instantiate an entity based on this ID, the situation is simple. We pass this ID as the $id_or_where parameter to the load method, and it doesn't even have to be an instance of the controller ID, but a simple value. We showed an example right at the beginning.

Loading using an ID made up of multiple properties

If the identification consists of more than one property, this must be taken into account. One option is to create an instance of controller ID, populate it, and use it to load the record. Suppose that in the sample application, you only want to get a localized version of the article based on the article ID - that is, a subentity. It can be done as follows:

Loading by query

Realistically, there is a situation where you know that you need one particular object, but you don't know its ID, but you know its other unique attributes. Therefore, you can pass a query to the load method. For an example, I'll reach traditionally into a sample application. In this one, we need to retrieve an instance of an article based on its URL (or part of the URL - the path) and based on the current locale so that the article can be displayed:

Parameter $load_filter

The second parameter of the load method has not escaped your attention. It is used to activate the limited loading mode. It is related to loading in general and we will explain it separatly.

Loading multiple objects (list loading)

There are two ways to load a list of objects based on criteria. The result of the load is similar, but the load methods themselves are designed for different uses.

Method ::fetchInstances( )

Right off the bat, let's note that this method doesn't return a list of objects as an array, but an instance of Jet\DataModel_Fetch_Instances, which is an iterator.

Data loading is not done immediately after the iterator instance is created.

The iterator also allows you to set pagination and sorting (see below for sorting).

Only when the application actually wants to work with the list of items, the iterator takes care of loading data from the database and creating instances. And it does it in a specific way. There can be tens of thousands or hundreds of thousands of records in the database - just a lot of data. That's why Jet\DataModel_Fetch_Instances works by not loading all the data straight away, but based on criteria, it loads, sorts and possibly trims (record from X to Y - e.g. pagination) only the information used as record IDs. And only on the basis of the retrieved record IDs does it load the records themselves. Thus, if you need to browse, for example, a list of products of an e-shop that has tens of thousands of them, this mechanism will make sure that until the last moment it works with the minimum of data and uploads only the data that the application needs for the end user (for example, only 50 products for the current list page in the administration).

Method ::fetch( )

You could say that the ::fetch() method is low-level. It is intended to directly load a list of data. No iterator as an intermediate step, no delay and based on clear criteria. So there is no "magic" going on here. It is important to clarify the parameters of this method:

• $where_per_model
  This is an associated field, where the key is the entity name (possibly an empty value or the keyword this - both identifying the current entity) and the value is the query that will be used to load the data for this entity/subentity. Thus, one query can define what the main entities will be uploaded and other queries can define what subentities will be uploaded.
• $order_by
  List sort definition. See below.
• $item_key_generator
  If it is necessary that the keys of the array of the resulting list have a certain value (it is an associated array), then it is possible to pass a key generator.
  The generator prototype: function( DataModel $item ) : string
• $load_filter
  Restricted loading mode. See below.

Metoda ::fetchIDs( )

It may be that you don't need instances of entities, but only a list of their identifiers - that is, instances of controller IDs.

This is enabled by the ::fetchIDs method, which is actually equivalent to the ::fetchInstances( ) method.

Thus, the method also does not immediately return a list, but returns an iterator instance - this time Jet\DataModel_Fetch_IDs, which provides a list of record identifiers.

Otherwise, the philosophy and behavior is identical to the ::fetchInstances( ) method..

Methods ::dataFetch*( )

Of course, it is not always a good idea to create lots of instances of some objects. To perform certain tasks, it is convenient (if not necessary) to work directly with the data without object instances as an intermediate layer. For example, if you have to do some maintenance and recalculations over tens of thousands of records (or more), then it is simply necessary - and it doesn't have to be just data maintenance, of course.

That's why Jet DataModel offers the ability to upload raw data and also has additional options for working with raw data. Yes, it does offer the ability to directly use Jet Db and perform classic queries. You have that option and sometimes it can be perfectly legitimate. However, you may lose portability and other benefits of ORM, for example. Nothing is black and white - it always depends on the specific situation.

For fetching raw data there is a set of static methods whose name always starts with ::dataFetch*. The parameters of these methods are the same, only the form of the output data differs.

Method parameters ::dataFetch*

• $select
  Definition of SELECT - see below.
• $where
  WHERE part of the query.
• $group_by>
  Definition of GROUP BY - see below.
• $having
  HAVING part of the query, which is formed in the same way as WHERE, except that expressions can refer to SELECT items (see below).
• $order_by
  Definition of sorting - see below.
• $limit
  Limit the number of records load.
• $offset
  Offset of loaded records.

List of methods ::dataFetch*

• dataFetchAll
  Simply load all data.
• dataFetchAssoc
  The first property will be taken as the key of the resulting list.
• dataFetchCol
  The result will be a simple one-dimensional array consisting of the first recorded property of the entity.
• dataFetchPairs
  Returns an associated array where the key is the first specified property and the value is the second.
• dataFetchRow
  Returns only one row.
• dataFetchOne
  Returns only one value.

Specifications SELECT, HAVING and GRUOP BY

Again, let's go straight to a practical example. Suppose you want to find out what basic event class is most common in your administration. So you need to calculate the honesty (use a common SQL function), you will also need GROUP BY. And to make it even more interesting, you'll only want to know the items whose frequency is greater than or equal to 20 - so you can't do that without HAVING either.

It is possible and one example is better than a thousand words:

While the example above is for loading raw data, SELECT, GROUP BY and HAVING definition is possible for any query represented by the Jet\DataModel_Query class.

For example, the query definition instance is held by the fetch iterators (Jet\DataModel_Fetch_Instances and Jet\DataModel_Fetch_IDs) and the query definition represented by the Jet\DataModel_Query class instance has the necessary methods to set up SELECT, GROUP BY, HAVING, WHERE and ORDER BY.

Ordering

Ordering can be used in more than one place, but before we show that, let's look at a general rule for defining ordering.

The sorting is defined as an array (because it is of course possible to sort by multiple properties) that contains the property names in the entity_name.property_name format, as in queries, or the SELECT element can be used (see the SELECT specification). The difference is that this value can be preceded by a + or a -. The + means ascending sorting and the - means descending sorting. Ascending order is the default and the + character is therefore not necessary.

If we are going to sort only by one property, it is not necessary to specify the sorting as an array and it is possible to use the reference of this property or the SELECT element.

Thus, the ordering can be defined as follows:

Now where to use the shift:

• As a parameter of the ::fetch( ) and ::dataFetch*() methods
  
• Setting the iterator order
  The ::fetchInstances( ) and ::fetchIDs( ) methods return an instance of the iterator Jet\DataModel_Fetch_Instances and Jet\DataModel_Fetch_IDs.
  For both of these iterators, the sorting can be set via access to the generated query as follows: $articles = Content_Article::fetchInstances();
$articles->getQuery()->setOrderBy([
    '-article.date_time'
]);
• Set the default ordering for a subentity that is in a 1:N relation within the definition:
  #[DataModel_Definition(
default_order_by: ['property_name','-next_property_name', '+some_property_name']
)]
• Set the default ordering for the subentity that is in the 1:N relation using the setLoadRelatedDataOrderBy method:
  SomeSubentity::setLoadRelatedDataOrderBy( ['property_name','-next_property_name', '+some_property_name'] );

Restricted loading mode

In practice, it is advisable to avoid uploading data that we do not need. Suppose you need a list of articles, but only their metadata, headlines and annotation, not the text of the article itself. How to do it?

Most load methods (except ::fetchData() ) have a parameter $load_filter. The value of this parameter is either an array (more useful in real practice) or a straight instance of the Jet\DataModel_PropertyFilter class - both represent a load filter, i.e. a rules for what to upload.

Note: The array is always converted to an instance of Jet\DataModel_PropertyFilter and only this instance is used internally.

Let's finally show how to use the filter in practice using the example with articles:

As you can see, the filter is really good in practice to define as an array. And it actually has simple rules. Again, an array is made up of references to entities and their properties. However, instead of a property name, you can use the * character. And yes, it says that we want to retrieve all the properties of the entity.

Important note: If an object is loaded in restricted mode, it cannot be saved or deleted to avoid theoretical data inconsistency. Objects uploaded in this way are read-only and attempting to save or delete them will result in an exception being thrown.

Recommendations

Let me conclude this chapter with a recommendation. Jet DataModel can operate with SQL function calls, it can HAVING, it uses GROUP BY.

But through practice I have come to the conclusion that it is good to avoid anything complicated within SQL. Instead of SQL functions, I prefer to store pre-prepared data or leave the postprocessing of data to PHP. It just makes everything so that the relational database is a good servant (or rather a partner), but not a master. And please, I may have once been a SQL fanatic :-)