Bitrix Framework

Create, update, delete, and select data makes quite sizeable functionality sufficient for the organization of work with unrelated data. However, web projects often imply relations among entities. That is why various ways of relating the entities are provided for:

1:1 Relations

The simplest type of relation is when an entity element refers to an element of another or the same entity. The examples described a deal with a book catalog, but until now book entries contained no indication of their authors. Let us implement that. First, let us describe the entity The author of the book:

<?php

namespace SomePartner\MyBooksCatalog;

use Bitrix\Main\Entity;

class AuthorTable extends Entity\DataManager
{
	public static function getTableName()
	{
		return 'my_book_author';
	}

	public static function getMap()
	{
		return array(
			new Entity\IntegerField('ID', array(
				'primary' => true,
				'autocomplete' => true
			)),
			new Entity\StringField('NAME'),
			new Entity\StringField('LAST_NAME')
		);
	}
}

This example is based on the assumption that a book has only one author which is a person with a first name and a last name, and an entry about such author is contained in the entity Author. At the same time, one author may have many books. Thus, we obtain the relation 1 author : N books.

This relation can be described as follows:

class BookTable extends Entity\DataManager
{
	...
	public static function getMap()
	{
		return array(
			...
			new Entity\IntegerField('AUTHOR_ID'),
			new Entity\ReferenceField(
				'AUTHOR',
				'SomePartner\MyBooksCatalog\Author',
				array('=this.AUTHOR_ID' => 'ref.ID')
			)
		);
	}
	...
}

First, we should add a numeric field AUTHOR_ID where the author’s ID will be stored. Based on this field, the relation between entities will be configured through a new type of field – ReferenceField. This is a virtual field that has no actual reflection in the database:

new Entity\ReferenceField(
	'AUTHOR',
	'SomePartner\MyBooksCatalog\Author',
	array('=this.AUTHOR_ID' => 'ref.ID')
	array('join_type' => 'LEFT')
)
// LEFT JOIN my_book_author ON my_book.AUTHOR_ID = my_book_author.ID

Now the described relation can be used during data sampling:

BookTable::getList(array(
	'select' => array('TITLE', 'AUTHOR.NAME', 'AUTHOR.LAST_NAME')
));

SELECT 
	`somepartner_mybookscatalog_book`.`TITLE` AS `TITLE`,
	`somepartner_mybookscatalog_author`.`NAME` AS `SOMEPARTNER_MYBOOKSCATALOG_AUTHOR_NAME`,
	`somepartner_mybookscatalog_author`.`LAST_NAME` AS `SOMEPARTNER_MYBOOKSCATALOG_AUTHOR_LAST_NAME`
FROM `my_book`
LEFT JOIN `my_book_author` `somepartner_mybookscatalog_author` ON `somepartner_mybookscatalog_book`.`AUTHOR_ID` = `somepartner_mybookscatalog_author`.`ID`

The switch to the Author entity is carried out by the entry AUTHOR: reference field name is specified, and the context switches to this entity after the dot. After that, the field name from this entity, including the Reference, can be specified, thus going even further and generating a new table connection:

'select' => array('AUTHOR.CITY.COUNTRY.NAME')

This could be a query to select a book author’s residence country if there was the structure Countries -> Cities -> Book authors living in the cities..

In order to make sure that the field names of different entities do not overlap, the system generates unique aliases for the entities connected. Sometimes they are not very readable. In these cases, we can use the reassignment of aliases you already know:

BookTable::getList(array(
	'select' => array(
		'TITLE',
		'AUTHOR_NAME' => 'AUTHOR.NAME',
		'AUTHOR_LAST_NAME' => 'AUTHOR.LAST_NAME'
	)
));

SELECT 
	`somepartner_mybookscatalog_book`.`TITLE` AS `TITLE`,
	`somepartner_mybookscatalog_author`.`NAME` AS `AUTHOR_NAME`,
	`somepartner_mybookscatalog_author`.`LAST_NAME` AS `AUTHOR_LAST_NAME`
FROM ...

Similarly to the source entity, the symbol * can be used to select all the scalar fields of an entity. Short aliases can also be used:

BookTable::getList(array(
	'select' => array(
		'TITLE',
		'AR_' => 'AUTHOR.*'
	)
));

SELECT 
	`somepartner_mybookscatalog_author`.`ID` AS `AR_ID`,
	`somepartner_mybookscatalog_author`.`NAME` AS `AR_NAME`,
	`somepartner_mybookscatalog_author`.`LAST_NAME` AS `AR_LAST_NAME`
FROM `my_book` `somepartner_mybookscatalog_book` 
LEFT JOIN `my_book_author` `somepartner_mybookscatalog_author` ON `somepartner_mybookscatalog_book`.`AUTHOR_ID` = `somepartner_mybookscatalog_author`.`ID`

As mentioned above, the conditions for the entity relation are described similarly to a filter. This means that tables can be connected by several fields, and SqlExpression can also be used:

$author_type = 5;

new Entity\ReferenceField(
	'AUTHOR',
	'SomePartner\MyBooksCatalog\Author',
	array(
		'=this.AUTHOR_ID' => 'ref.ID',
		'=ref.TYPE' => new DB\SqlExpression('?i', $author_type)
	)
)
// LEFT JOIN my_book_author ON my_book.AUTHOR_ID = my_book_author.ID AND my_book_author.TYPE = 5

The field ReferenceField, like other fields, can be described during data selection in the ‘runtime’ section and be used for the connection of other entities with which the relations were not initially described.

If a field of an adjacent entity is used frequently, ExpressionField can be used, and the remote field can be determined as local.

new Entity\ExpressionField('AUTHOR_NAME', '%', 'AUTHOR.NAME')

In this example, the difference is not evident, but if you have a longer chain of junctions instead of AUTHOR.NAME, the use of one short name may come in handy.

Relation 1:N, or back Reference

The ReferenceField concept means that this field must be located in the entity N of the relation 1:N. Thus, the Reference must indicate just one entry: in the example above, it is assumed that a book may have only 1 author, and thus the ReferenceField in the Book entity indicates one entry of the Author entity.

It is easy to select a book author because the Book entity has an indication of the relation with the author entity. But how can we select all of the books of an author if the Author entity contains no explicit indication to the Books?

The point is that the Reference described in the book entity is sufficient for two-way selection; we only have to use a special syntax:

\SomePartner\MyBooksCatalog\AuthorTable::getList(array(
	'select' => array(
		'NAME',
		'LAST_NAME',
		'BOOK_TITLE' => '\SomePartner\MyBooksCatalog\BookTable:AUTHOR.TITLE'
	)
));

SELECT 
	`somepartner_mybookscatalog_author`.`NAME` AS `NAME`,
	`somepartner_mybookscatalog_author`.`LAST_NAME` AS `LAST_NAME`,
	`somepartner_mybookscatalog_author_book_author`.`TITLE` AS `BOOK_TITLE`
FROM `my_book_author` `somepartner_mybookscatalog_author` 
LEFT JOIN `my_book` `somepartner_mybookscatalog_author_book_author` ON `somepartner_mybookscatalog_author_book_author`.`AUTHOR_ID` = `somepartner_mybookscatalog_author`.`ID`

Instead of the Reference name, we have to indicate the Name of an entity which has a Reference to the current entity:Name of the reference to the current entity. Following such construction, the context switches to the Book entity, and the TITLE field and other fields can be selected in it.

Relations M:N

Any book can be characterized from the point of view of genre/category, whether business or fiction literature, history or training books, thrillers, comedies, dramas, books on marketing, development, sales, etc. For simplicity sake, let us call all of this as tags, and assign several tags to each book.

Let us describe the entity of tags:

<?php

namespace SomePartner\MyBooksCatalog;

use Bitrix\Main\Entity;

class TagTable extends Entity\DataManager
{
	public static function getTableName()
	{
		return 'my_book_tag';
	}

	public static function getMap()
	{
		return array(
			new Entity\IntegerField('ID', array(
				'primary' => true,
				'autocomplete' => true
			)),
			new Entity\StringField('NAME')
		);
	}
}

In order to connect this entity with the Books using the principle N:M (one book may have several tags, one tag may be connected with several books), it is necessary to create in the database an interim entity with a table to store data about the connections of books with tags.

< true
			)),
			new Entity\ReferenceField(
				'BOOK',
				'SomePartner\MyBooksCatalog\Book',
				array('=this.BOOK_ID' => 'ref.ID')
			),
			new Entity\IntegerField('TAG_ID', array(
				'primary' => true
			)),
			new Entity\ReferenceField(
				'TAG',
				'SomePartner\MyBooksCatalog\Tag',
				array('=this.TAG_ID' => 'ref.ID')
			)
		);
	}
}

In this case, the entity is just required to store the connection Book ID – tag ID, and relevant ReferenceFields will help to describe this connection in software.

The interim entity itself may be of little interest. Standard tasks in this case involve obtaining a list of tags for a book or a list of books according to a tag. These tasks are solved using the methods of work with the Reference described above:

// tags for the book with ID = 5
\SomePartner\MyBooksCatalog\BookTable::getList(array(
	'filter' => array('=ID' => 5),
	'select' => array(
		'ID',
		'TITLE',
		'TAG_NAME' => 'SomePartner\MyBooksCatalog\BookTag:BOOK.TAG.NAME'
	)
));

SELECT 
	`somepartner_mybookscatalog_book`.`ID` AS `ID`,
	`somepartner_mybookscatalog_book`.`TITLE` AS `TITLE`,
	`somepartner_mybookscatalog_book_book_tag_book_tag`.`NAME` AS `TAG_NAME`
FROM `my_book` `somepartner_mybookscatalog_book` 
LEFT JOIN `my_book_to_tag` `somepartner_mybookscatalog_book_book_tag_book` ON `somepartner_mybookscatalog_book_book_tag_book`.`BOOK_ID` = `somepartner_mybookscatalog_book`.`ID`
LEFT JOIN `my_book_tag` `somepartner_mybookscatalog_book_book_tag_book_tag` ON `somepartner_mybookscatalog_book_book_tag_book`.`TAG_ID` = `somepartner_mybookscatalog_book_book_tag_book_tag`.`ID`
WHERE `somepartner_mybookscatalog_book`.`ID` = 5

The entry SomePartner\MyBooksCatalog\BookTag:BOOK.TAG.NAME may seem complicated, but it is actually pretty simple when considered by parts:

The call chain will be very similar in order to obtain books with a specific tag:

// books for the tag with ID = 11
\SomePartner\MyBooksCatalog\TagTable::getList(array(
	'filter' => array('=ID' => 11),
	'select' => array(
		'ID',
		'NAME',
		'BOOK_TITLE' => 'SomePartner\MyBooksCatalog\BookTag:TAG.BOOK.TITLE'
	)
));

Thus, using two types of switch between entities, REFERENCE and Entity:REFERENCE, the necessary related information can be easily accessed.