Scalar Joins

Smash and grab values from another table without needing to create a child entity.

A scalar join is a special type of relationship that fetches a single item of data from a separate database table. So instead of joining a parent object to a child object, it just appears as a scalar property value on the parent - there is only a single entity involved (but two database tables). This is mostly used for cases where your database stores an abbreviation, id, or code in the table where your entity is stored, but you want the full description to appear on a property of the parent entity, and you have to look up that description on a separate table.

For example, suppose you have an entity named Schedule, and it has a property named dayOfWeek, in which you would like to store the day of the week as a human-readable string (eg. 'Monday'). The code for this entity looks like this:

class Schedule
{
    public int $id;
    public string $description;
    public string $dayOfWeek;
}

The records for this entity are stored in a table named schedule that looks like this:

schedule
id
description
day_number

The value for the day of the week in this example is stored as an integer from 0 to 6, and there is a separate table that matches up the numbers with human-readable days of the week:

days
id
day_name

In order to populate the value of our dayOfWeek property on our Schedule entity with the value stored in day_name on the days table, we need to join the schedule table to the days table using the day_number of the entity's table to join to the id in the lookup table and we do this using a scalar join mapping.

Example

Here is an example of how to set up the mapping for a scalar join using attributes or annotations (these examples are all equivalent; you only need to use one type of mapping provider, it is up to you which one).

PHP8+ Attributes

use Objectiphy\Objectiphy\Table;
use Objectiphy\Objectiphy\Column;
use Objectiphy\Objectiphy\Relationship;

#[Table(name: 'schedule')]
class Schedule
{
    #[Column(isPrimaryKey: true)]
    public int $id;
    
    #[Column]
    public string $description;
    
    #[Relationship(
        relationshipType: 'scalar',
        targetScalarValueColumn: 'days.day_name',
        joinTable: 'days',
        sourceJoinColumn: 'day_number',
        targetJoinColumn: 'id',
        type: 'string'
    )]
    public string $dayOfWeek;
}

Objectiphy Annotations

use Objectiphy\Objectiphy\Table;
use Objectiphy\Objectiphy\Column;
use Objectiphy\Objectiphy\Relationship;

/**
 * @Table(name="schedule")
 */
class Schedule
{
    /**
     * @Column(isPrimaryKey=true)
     */ 
    public int $id;
    
    /**
     * @Column
     */ 
    public string $description;
    
    /**
     * @Relationship(
     *     relationshipType="scalar",
     *     targetScalarValueColumn="days.day_name",
     *     joinTable="days",
     *     sourceJoinColumn="day_number",
     *     targetJoinColumn="id",
     *     type="string"
     * )
     */
    public string $dayOfWeek;
}

Doctrine Annotations

Sorry! Scalar joins are not possible using Doctrine annotations.

Be aware that by default, scalar joins are read-only. If you want to update the column on the joined table with the value of the property, you will need to set the isReadOnly attribute to false on the annotation (or using a mapping override).

Also note that you cannot currently insert a value on the joined table using an entity with a scalar join. So in the above example, you would not be able to assign a brand new day ID code and day name to an entity and have it save a new record in the days table.