Get best of the week

Mapping data from a relational database

Sometimes situations arise when the solution to the problem of retrieving data from a relational database does not fit into the capabilities of the ORM used in the project, for example, either because of the insufficient speed of the ORM itself, or not quite optimal SQL queries generated by it. In this case, you usually have to write queries manually.

The problem is that the data from the database (including in response to the JOIN request) is returned as a “flat” two-dimensional array that does not reflect the complex “tree-like” data structure of the application. It is extremely inconvenient to work with such an array further, therefore, a more or less universal solution is required to bring this array into a more suitable form according to a given pattern.

The solution was found, convenient and fast enough.

How fast


To evaluate the speed of the library, I put together a small test bench on which the speed of my library is compared with the speed of Eloquent. For measurements, the phpbench package was used.

In order to deploy a stand at home:

git clone https://github.com/hrustbb2/env-arrayproc-bench.git
cd env-arrayproc-bench
./env

Here I used the tool described in my previous article .

Then in the menu we select: 1 Develop, then: 1 Build, then 2 Deploy and Up;
Then run the tests 5. Run tests

There are 3000 books in the database. The results are as follows:

+-----------------+-----+------+------+-------------+--------------+
| subject         | set | revs | iter | mem_peak    | time_rev     |
+-----------------+-----+------+------+-------------+--------------+
| benchEloquent   | 0   | 1    | 0    | 76,442,912b | 12,781.612ms |
| benchEloquentId | 0   | 10   | 0    | 5,123,224b  | 16.432ms     |
| benchProc       | 0   | 1    | 0    | 36,364,176b | 1,053.937ms  |
| benchProcId     | 0   | 10   | 0    | 4,462,696b  | 7.684ms      |
+-----------------+-----+------+------+-------------+--------------+

benchEloquent - pulls out all the books with the authors using Eloquent
benchEloquentId - pulls out a certain book with the authors using Eloquent (10 times)

benchProc - pulls out all the books with the authors using the library
benchProcId - pulls out a certain book with the authors using the library (10)

to actuate tests are not representative enough, but the difference is noticeable, both in runtime and in memory consumption.

How it works


Install:

composer require hrustbb2/arrayproc:v1.0.0

Further, for an example (extremely simple), imagine that we have a database of books and authors with the following structure.

image

The task is to pull out all the books with their authors.

The request will look something like this:

SELECT
    books.id,
    books.name,
    authors.id,
    authors.name
FROM
    books
LEFT JOIN relations ON relations.books_id = books.id
LEFT JOIN authors ON authors.id = relations.authors_id

In response, we get about such an array of data.

book.idbook.nameauthor.idauthor.name
1book12author2
1book14author4
1book16author6
2book22author2
2book23author3
2book26author6
2book27author7


The array is two-dimensional, some fields are duplicated, for convenience you need to convert it
[
	1 => [
		'id' => 1,
		'name' => 'book1',
		'authors' => [
			2 => [
				'id' => 2,
				'name' => 'author2'
			],
			4 => [
				'id' => 4,
				'name' => 'author4'
			],
			6 => [
				'id' => 6,
				'name' => 'author6'
			],
		]
	],
	2 => [
		'id' => 2,
		'name' => 'book2',
		'authors' => [
			2 => [
				'id' => 2,
				'name' => 'author2'
			],
			3 => [
				'id' => 3,
				'name' => 'author3'
			],
			6 => [
				'id' => 6,
				'name' => 'author6'
			],
			7 => [
				'id' => 7,
				'name' => 'author7'
			],
		]
	],
]


To do this, slightly modify our request:


SELECT
    books.id AS book_id,
    books.name AS book_name,
    authors.id AS author_id,
    authors.name AS author_name
FROM
    books
LEFT JOIN relations ON relations.books_id = books.id
LEFT JOIN authors ON authors.id = relations.authors_id

Here we set aliases in the SELECT section: for fields with data about books, aliases with the prefix 'book_', and for fields with information about authors with the prefix 'author'.

Next, we convert the database response

use hrustbb2\arrayproc\ArrayProcessor;

$arrayProcessor = new ArrayProcessor();
$config = [
	'prefix' => 'book_',
	'authors' => [
		'prefix' => 'author_',
	]
]
$booksData = $arrayProcessor->process($conf, $rows)->resultArray();

where:

$ rows is the database response in the form of an array of objects / stdClass ()
$ config is an associative array reflecting the data structure of the resulting array

As a result, in $ booksData we have a tree-like array having the structure described in $ config, filled with the corresponding data.

Something like this.

More articles:


All Articles