The similarity of LINQ to PHP for the EAV data storage model
Having seen a post about LINQ in PHP , I decided to immediately share my best practices in this area.
My implementation is far from a full LINQ, but it contains the most noticeable feature of the technology - the lack of a foreign query string.
My activity, both working and not very, is related to the database, which has an EAV data storage model. This means that with an increase in the number of entities, the number of tables does not increase. All information is stored in just two tables.
Tables with data in the EAV model
Naturally, in order to get a “record” from such a structure, it is necessary to write a query that is completely different from a similar query with the usual database structure.
For instance:
and in eav
As the saying goes - feel theass 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 that contain 50% - 70% of the supporting code. Then the idea came up to generate a request automatically. So IQB - Irro Query Builder was born. His concept was inspired by the way Drupal interacts with the database.
The above query in IQB will look like this:
The amount of code has not decreased, but readability, it seems to me, has increased.
This request uses all the basic methods to generate the request.
The from () method accepts a class or an array of classes representing database tables. There are only two tables, so there are the same number of classes. The constructor of the table class accepts the alias of the table, its conditional type and data type, if it is an attribute table.
The table alias is used in all other methods of the query generator. The conditional type, for the table of objects, is the name of the entity among which the search is conducted, and for the table of attributes, the conditional type is needed simply to distinguish the attributes of one object. Type of data, tells which field of the table to take data from. This is necessary because the attribute of an object is a structure with 4 fields for data, of which only one is used, and in which field the data is stored must be specified explicitly.
The where () method imposes conditions on the selection. It always takes 3 arguments: table alias, condition, value. Depending on the condition, the alias of another table, the value or an array with the value with which the table field is compared, can be passed as a value.
For instance:
set the 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 turn out (if the attribute data type is changed to string)
As conditions, you can use the lines '=', '! =', '> =', '<=', '>', '<', 'Like' and 'with' - the attribute belongs to a specific object.
The select () method tells the generator which table values should fall into the selection. In addition, you can "wrap" this value in a function by passing a line 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 is the alias of the field in the selection.
Together with the groupBy () and orderBy () methods, this is enough to build the average read requests.
However, not all so simple.
Objects, like entities in ordinary databases, can be connected by relationships.
Strange as it may seem, communication is also an object. With attributes. And in order to get object B, which is a child of object A, you need to do the following manipulations:
Too much for a simple “take B subsidiary from A”. To automate the binding of objects, IQB has a linked () method .
The method accepts an ID_object or an alias of a known object, an alias of a child / parent, and a “U-flag” hint - look for child objects or parent objects. Thus, the above code can be written like this:
It would be possible to finish this, but there are occasionally tasks for which the query generator is somewhat limited. For example, for some time now objects have begun to come across in which some attribute may be missing. To solve this problem, the joinTo () method was added which makes the LEFT JOIN of the attribute table to the object table.
And for very exotic requests there are rawWhere () and rawSelect () which allow you to enter arbitrary pieces of the request.
I did not try to make the library for general use, therefore I introduced new opportunities only when the need arose. In this regard, design errors made at the early stages of development, overgrown with a couple of layers of crutches, necessary for compatibility with the old code and to support new functions.
Despite the possibility of realizing fairly complex queries using IQB, it can only be called flexible with a stretch. Therefore, now 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 a full LINQ, but it contains the most noticeable feature of the technology - the lack of a foreign query string.
What for?
My activity, both working and not very, is related to the database, which has an EAV data storage model. This means that with an increase in the number of entities, the number of tables does not increase. All information is stored in just two tables.
Tables with data in the EAV model
Naturally, in order to get a “record” from such a structure, it is necessary to write a query that is completely different from a similar query with the usual database structure.
For instance:
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 the saying goes - feel the
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 that contain 50% - 70% of the supporting code. Then the idea came up to generate a request automatically. So IQB - Irro Query Builder was born. His concept was inspired by the way Drupal interacts with the database.
The above query in IQB will look like this:
$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 request uses all the basic methods to generate the request.
The from () method accepts a class or an array of classes representing database tables. There are only two tables, so there are the same number of classes. The constructor of the table class accepts the alias of the table, its conditional type and data type, if it is an attribute table.
The table alias is used in all other methods of the query generator. The conditional type, for the table of objects, is the name of the entity among which the search is conducted, and for the table of attributes, the conditional type is needed simply to distinguish the attributes of one object. Type of data, tells which field of the table to take data from. This is necessary because the attribute of an object is a structure with 4 fields for data, of which only one is used, and in which field the data is stored must be specified explicitly.
The where () method imposes conditions on the selection. It always takes 3 arguments: table alias, condition, value. Depending on the condition, the alias of another table, the value or an array with the value with which the table field is compared, can be passed as a value.
For instance:
$q->where('attr','with','object');
set the 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 turn out (if the attribute data type is 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 tells the generator which table values should fall into the selection. In addition, you can "wrap" this value in a function by passing a line 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 is the alias of the field in the selection.
Together with the groupBy () and orderBy () methods, this is enough to build the average read requests.
However, not all so simple.
Objects, like entities in ordinary databases, can be connected by relationships.
Strange as it may seem, communication is also an object. With attributes. And in order to get object B, which is a child of object A, you need 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 subsidiary from A”. To automate the binding of objects, IQB has a linked () method .
The method accepts an ID_object or an alias of a known object, an alias of a child / parent, and a “U-flag” hint - 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 there are occasionally tasks for which the query generator is somewhat limited. For example, for some time now objects have begun to come across in which some attribute may be missing. To solve this problem, the joinTo () method was added which makes the LEFT JOIN of the attribute table to the object table.
And for very exotic requests there are rawWhere () and rawSelect () which allow you to enter arbitrary pieces of the request.
Conclusion
I did not try to make the library for general use, therefore I introduced new opportunities only when the need arose. In this regard, design errors made at the early stages of development, overgrown with a couple of layers of crutches, necessary for compatibility with the old code and to support new functions.
Despite the possibility of realizing fairly complex queries using IQB, it can only be called flexible with a stretch. Therefore, now 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.
