PHP LINQ similarity for EAV data storage model
After seeing the post about LINQ in PHP , I decided to immediately share my work in this area.
My implementation is far from being a full-fledged LINQ, but it contains the most noticeable feature of the technology - the absence of a foreign query string.
My activity, both working and not so, is related to the database, which has an EAV data storage model. This means that as the number of entities increases, the number of tables does not increase. All information is stored in just two tables.
Data tables in the EAV model
Naturally, in order to get a “record” from such a structure, it is necessary to write a query completely different from a similar query with the usual database structure.
For example:
and in EAV
As they say - feelass difference.
The situation is complicated by the fact that many objects are interconnected by relationships that likewise inflate a request.
At one point, I was tired of writing poorly readable noodles, which contains 50% - 70% of the auxiliary code. Then there was an idea to generate a query automatically. So IQB - Irro Query Builder came to light. His concept was inspired by how the interaction with the database in Drupal is arranged.
The above request in IQB will look as follows:
The amount of code has not decreased, but readability, it seems to me, has increased.
This query uses all the basic methods for generating a query.
The from () method accepts a class or an array of classes representing database tables. There are only two tables, so the same number of classes. The constructor of the table class accepts the alias of the table, its conditional type, and the data type if it is an attribute table.
The table alias is used in all other query generator methods. The conditional type, for the table of objects, is the name of the entity among which the search is conducted, and for the attribute table, the conditional type is necessary just to distinguish the attributes of a single object. The data type tells which field of the table to take the data from. This is necessary because An object attribute is a structure with 4 fields for data, of which only one is used, and in which field the data is stored must be explicitly specified.
The where () method imposes conditions on the sample. It always takes 3 arguments: alias of the table, condition, value. Depending on the condition, an alias of another table may be passed as a value, a value or an array of values ​​with which the table field is compared.
For example:
will set a condition
from such an expression
get a similar, but completely different expression
and if the object table was not declared in from (), then this will be obtained (if the attribute data type is also changed to string)
As conditions, you can use the lines '=', '! =', '> =', '<=', '>', '<', 'Like' and 'with' - the attribute belongs to a specific object.
The select () method indicates to the generator which table values ​​should fit into the selection. In addition, you can “wrap” this value into a function by passing a string like “SUM ($)” to the method with the third argument, and instead of a dollar, the table field is substituted into the function. The second argument can be the pseudonym of the field in the selection.
Together with the groupBy () and orderBy () methods, this is enough for building average read requests.
However, not all so simple.
Objects, like entities in ordinary databases, can be related by relationships.
Communication, oddly enough, is also an object. With attributes. And in order to get object B, which is a child of object A, it is necessary to do the following manipulations:
Too much for a simple "take B child of A". To automate the binding of objects, in IQB there is a method linked () .
The method accepts an ID_object or alias of a known object, an alias of the child / parent and a “reversal flag” i.e. instruction - look for child objects or parent objects. Thus, the above code can be written like this:
It would be possible to finish this, but from time to time there are tasks for which the query generator turns out to be somewhat limited. For example, for some time, objects have started to come across for which some attribute may be missing. To solve this problem, the joinTo () method was added which makes the LEFT JOIN attribute table to the object table.
And for quite exotic requests, there are rawWhere () and rawSelect () which allow you to enter arbitrary pieces of the request.
')
I did not try to make a library for general use, so I introduced new features only when the need arose. In this connection, design errors made in the early stages of development were overgrown with a pair of crutches needed for compatibility with the old code and for supporting new functions.
Without the possibility of realizing quite complex queries with the help of IQB, it can only be called flexible with a stretch. Therefore, the concept of a more flexible generator is being formed, which will allow to further reduce the number of characters when setting the query condition, but this is a completely different story.
My implementation is far from being a full-fledged LINQ, but it contains the most noticeable feature of the technology - the absence of a foreign query string.
What for?
My activity, both working and not so, is related to the database, which has an EAV data storage model. This means that as the number of entities increases, the number of tables does not increase. All information is stored in just two tables.
Data tables in the EAV model
Naturally, in order to get a “record” from such a structure, it is necessary to write a query completely different from a similar query with the usual database structure.
For example:
SELECT field_1, field_2, field_3 FROM object and in EAV
SELECT f1.value_bigint, f2.value_bigint, f3.value_bigint FROM objects ob, attributes_values f1, attributes_values f2, attributes_values f3 WHERE ob.ID_type="object" AND f1.ID_object = ob.ID_object AND f1.ID_attribute = 1 AND f2.ID_object = ob.ID_object AND f2.ID_attribute = 2 AND f3.ID_object = ob.ID_object AND f3.ID_attribute = 3
As they say - feel
The situation is complicated by the fact that many objects are interconnected by relationships that likewise inflate a request.
Query generator
At one point, I was tired of writing poorly readable noodles, which contains 50% - 70% of the auxiliary code. Then there was an idea to generate a query automatically. So IQB - Irro Query Builder came to light. His concept was inspired by how the interaction with the database in Drupal is arranged.
The above request in IQB will look as follows:
$q = new IQB(); $query = $q->from(array(new IQBObject('ob','object'), new IQBAttr('f1',1,INT), new IQBAttr('f2',2,INT), new IQBAttr('f3',3,INT) )) ->where('f1','with','ob')->where('f2','with','ob')->where('f3','with','ob') ->select('f1')->select('f2')->select('f3') ->build();
The amount of code has not decreased, but readability, it seems to me, has increased.
This query uses all the basic methods for generating a query.
The from () method accepts a class or an array of classes representing database tables. There are only two tables, so the same number of classes. The constructor of the table class accepts the alias of the table, its conditional type, and the data type if it is an attribute table.
The table alias is used in all other query generator methods. The conditional type, for the table of objects, is the name of the entity among which the search is conducted, and for the attribute table, the conditional type is necessary just to distinguish the attributes of a single object. The data type tells which field of the table to take the data from. This is necessary because An object attribute is a structure with 4 fields for data, of which only one is used, and in which field the data is stored must be explicitly specified.
The where () method imposes conditions on the sample. It always takes 3 arguments: alias of the table, condition, value. Depending on the condition, an alias of another table may be passed as a value, a value or an array of values ​​with which the table field is compared.
For example:
$q->where('attr','with','object');
will set a condition
attr.ID_object = object.ID_object
from such an expression
$q->where('attr','=','object');
get a similar, but completely different expression
attr.value_bigint = object.ID_object
and if the object table was not declared in from (), then this will be obtained (if the attribute data type is also changed to string)
attr.value_ntext = "object"
As conditions, you can use the lines '=', '! =', '> =', '<=', '>', '<', 'Like' and 'with' - the attribute belongs to a specific object.
The select () method indicates to the generator which table values ​​should fit into the selection. In addition, you can “wrap” this value into a function by passing a string like “SUM ($)” to the method with the third argument, and instead of a dollar, the table field is substituted into the function. The second argument can be the pseudonym of the field in the selection.
Together with the groupBy () and orderBy () methods, this is enough for building average read requests.
However, not all so simple.
Objects, like entities in ordinary databases, can be related by relationships.
Communication, oddly enough, is also an object. With attributes. And in order to get object B, which is a child of object A, it is necessary to do the following manipulations:
$q->from(array( new IQBObject('b','B'), new IQBAttr('parent',23,INT), new IQBAttr('child',24,INT) )) ->where('parent','=',123456) // ID_object ->where('child','with','parent') ->where('child','=','b')
Too much for a simple "take B child of A". To automate the binding of objects, in IQB there is a method linked () .
The method accepts an ID_object or alias of a known object, an alias of the child / parent and a “reversal flag” i.e. instruction - look for child objects or parent objects. Thus, the above code can be written like this:
$q->from(new IQBObject('b','B'))->linked(123456,'b');// .
It would be possible to finish this, but from time to time there are tasks for which the query generator turns out to be somewhat limited. For example, for some time, objects have started to come across for which some attribute may be missing. To solve this problem, the joinTo () method was added which makes the LEFT JOIN attribute table to the object table.
And for quite exotic requests, there are rawWhere () and rawSelect () which allow you to enter arbitrary pieces of the request.
')
Conclusion
I did not try to make a library for general use, so I introduced new features only when the need arose. In this connection, design errors made in the early stages of development were overgrown with a pair of crutches needed for compatibility with the old code and for supporting new functions.
Without the possibility of realizing quite complex queries with the help of IQB, it can only be called flexible with a stretch. Therefore, the concept of a more flexible generator is being formed, which will allow to further reduce the number of characters when setting the query condition, but this is a completely different story.
Source: https://habr.com/ru/post/209682/
All Articles