Creation of queries

After we have defined entity and saved it is time to deal with what to do with it. We will definitely need to load entities, but also delete them or work with their raw data. In doing all of this, however, we will already need to build queries.

You have probably already come across some questions. Either while exploring the sample application, or in the chapter on external relations. And it must have stuck out to you that queries take the form of an array. It's entirely possible that someone will get a "WTF?!" effect :-) But it has a clear logic and it's the best solution. We'll tell you why right away.

Queries = array? Why?!

If ORM is to be independent of the type of backend used - i.e. the database, it cannot rely on any particular SQL dialect for queries. This is obvious.

But what are the options?

The first suggestion is to use some universal SQL dialect and parse and transform it. But why the hell do that? Isn't that a straight bender? Developing (or integrating) some parser is a lot of unproductive work, a lot of unproductive code to take care of. There's no justification for it from a cost perspective. Simply parsing SQL to have SQL at the other end and spending resources (time = money) on it has no logic and absolutely no benefit. Not to mention that it also means overhead of machine time while the application is running.

Another option ... What about doing it somehow object-oriented and put queries in the form of method calls and have the query represented by an object model? Okay, that's actually how it is in Jet DataModel. The query really does have its own model and is represented by class instances. If you look into the depths of Jet DataModel, you will definitely come across this. But it's unusable for real-world applications (although technically you can use it). Why? Because very often we create, compose, assemble queries. For example, based on what filter the user has and a million other aspects. And for such usage, some method calls to something is really extremely inefficient and opaque.

I didn't even want to develop something nonsense to make it "supposedly sexy". To me, sexy is what's effective. And I didn't want the headache of object madness, which in this case would be counterproductive. That's why I chose this path, and queries in Jet DataModel are formulated as arrays. So for example like this:


$where = [];



if( $search ) {

    $search = '%' . $search . '%';



    $where[] = [

        'article_localized.title *'      => $search,

        'OR',

        'article_localized.text *'       => $search,

        'OR',

        'article_localized.annotation *' => $search,

    ];

}



if( $another_filter ) {

    if($where) {

        $where[] = 'AND';

    }



    $where[] = [

        'article.id' => another_filter

    ];

}

//... ... ...



$articles = static::fetchInstances( $where );

Basic principle

As you can see from the simple example above, the query combines a regular and an associated field.

The field keys form a reference to the corresponding entity property. The reference is formed as follows: entity_name.property_name. So this is where the entity name determined by the definition is used. An entity name is the name of any entity that is, however, in an internal and external relation.

You don't have to worry about linking tables. Jet DataModel does that automatically based on the relation definitions. The definition simply specifies how the relation should work and you simply use the relation in your queries.

The name of the property is clear - it corresponds exactly to the name of the entity class property (not to the column in the DB table - it may have a different name).

Your attention will not have escaped the char * after the reference. This is one of the operators. We'll look at the operators later.

The value of an array element (here, a query element) is the value that should be related to the query element. But it is important to note that a value can be an array. The backend can handle this and make the array a familiar ... AND article.id IN ( 5, 6, 99, 100) ...

This can be followed by the logical operator 'OR' or 'AND'. And these are no longer associated. Thus, we add logical operators to the query as to a regular array.

Last but not least, a query may consist of other queries. A field can be composed of other fields that form logical subsets of the query. It's the same thing as parentheses () in SQL. For clarity, here's how:


$where = [

    [

       'entity_x.property_a' => 1,

       'OR'

       'entity_x.property_b' => 2

    ],

    'AND',

    [

       'entity_y.property_a' => 3,

       'OR'

       'entity_y.property_b' => 4

    

    ]

];

Operators

Now for those operators that may be behind the reference. I write 'may' because the default '=' operator need not be specified, but at the same time the '=' operator need not mean '=' in the SQL sense, but the IN operator if an array value is used ... Let's rather show a table with a summary of all operators.

Operator	Meaning in SQL
= (Optional, default - no need to specify)	If the bound value is not an array, then the operator represents the regular operator = If the bound value is an array, then the operator represents SQL IN( ... )
!=	If the bound value is not an array, then the operator represents a regular operator <> If the bound value is an array, then the operator represents SQL NOT IN( ... )
*	LIKE '...'
!*	NOT LIKE '...'
>	In SQL the same meaning
<	In SQL the same meaning
>=	In SQL the same meaning
<=	In SQL the same meaning