how would i access the extra column of the pivot table?

[dreamsbond]

i am having a pivot table which store an extra column in boolean indicating whether to email notification shall be send to the user.

Could you tell how to access the pivot table column?

[neomerx]

In API class you either call $this->createBuilder($modelClass) or $this->getConnection()->createQueryBuilder(), apply filters and then $this->fetchResources($builder, $modelClass) or fetchResource or fetchRow.

$this->getConnection()->createQueryBuilder() is low-level function which could be used if only table name and columns known, if model (with columns/relationships/etc) is set up then framework functions that work with filters/relationships/etc could be reused. Fetch methods work with builders created in either way.

[dreamsbond]

So I will have to add a new (viruual) field in model and attribute in scheme?

How do achieve this? Like virtual password?

[dreamsbond]

could you provide a working sample of accessing extra column in pivot table?

[dreamsbond]

or literally, how do i make use of the API class to achieve reading from and writing into pivot table column (other than the foreign key).

i.e.:

        $this->createTable(Model::class, [
            $this->primaryInt(Model::FIELD_ID),
            $this->foreignRelationship(Model::FIELD_ID_PROJECT, Project::class, true),
            $this->foreignRelationship(Model::FIELD_ID_MEMBER, User::class, true),

            $this->bool(EXTRA_COLUMN_EMAIL_NOTIFICATION, false),

            $this->timestamps(),

            $this->unique([
                Model::FIELD_ID_PROJECT,
                Model::FIELD_ID_MEMBER,
            ]),
        ]);

[neomerx]

What signature the method should have? getProjectNotificationEmails(int $projectId): array?

It might be something like this

public function getProjectNotificationEmails(int $projectId): array
{
    $builder = $this->createBuilder(ProjectMember::class);

    $projectIdColumn = $builder->buildColumnName($builder->getAlias(), ProjectMember::FIELD_ID_PROJECT);
    $hasNotificationColumn = $builder->buildColumnName($builder->getAlias(), ProjectMember::EXTRA_COLUMN_EMAIL_NOTIFICATION);

    // join user table
    $userTableAlias  = $builder->createRelationshipAlias(ProjectMember::REL_MEMBER);
    $userEmailColumn = $builder->buildColumnName($userTableAlias, User::FIELD_EMAIL);

    $builder
        // select from joined table
        ->select($userEmailColumn)
        ->fromModelTable()
        // but apply conditions on the project_members table
        ->where($projectIdColumn . '=' . $builder->createPositionalParameter($projectId))
        ->andWhere($hasNotificationColumn . '=1');

    $emails = $this->fetchColumn($builder, User::class, User::FIELD_EMAIL);

    return $emails;
}

[dreamsbond]

got it, but i don't understand the linkage, that is how it can be accessed as an attribute in schema at the end.

[neomerx]

Can you quote the part you have difficulties with?

[dreamsbond]

i have no idea where exactly the place in API class to start with.
i guess it should be do by overriding the builderOnIndex()

    protected function builderOnIndex(ModelQueryBuilder $builder): ModelQueryBuilder
    {
        $tableColumn = ProjectMember::TABLE_NAME . '.' . ProjectMember::FIELD_EMAIL_NOTIFICATION;
        $builder->addSelect(ProjectMember::TABLE_NAME . '.' . ProjectMember::FIELD_EMAIL_NOTIFICATION);
        
        return parent::builderOnIndex($builder);
    }

[dreamsbond]

where the builder added the "ProjectMember::FIELD_EMAIL_NOTIFICATION" column in the result set.
and i dun know how to make it accessible in ProjectSchema.

[dreamsbond]

it looks like i have to do the same overriding on builderOnRead as well, as to include the "ProjectMember::FIELD_EMAIL_NOTIFICATION" column in the result set.

seems crumsy by the way.

[dreamsbond]

or i just override the createIndexModelBuilder...

[neomerx]

I think you don't need to override anything. I've added an additional condition for EXTRA_COLUMN_EMAIL_NOTIFICATION column. Just put the sample to API class and adopt for your class names.

[dreamsbond]

this is what i schema looks like.

{
    "data": [
        {
            "type": "projects",
            "id": "1",
            "attributes": {
                "name": "COMPUTER VISION STUDY",
                "created-at": "2017-01-01T09:00:01+0800",
                "updated-at": null
            },
            "relationships": {
                "members": {
                    "data": [
                        {
                            "type": "users",
                            "id": "1"
                        }
                    ]
                }
            },
            "links": {
                "self": "/api/v1/projects/1"
            }
        }
    ],
    "included": [
        {
            "type": "users",
            "id": "1",
            "attributes": {
                "surname": "Wiegand",
                "first-name": "Johann",
                "other-name": null,
                "email": "[email protected]",
                "created-at": "2019-01-23T17:18:34+0800",
                "updated-at": null
            },
            "relationships": {
                "role": {
                    "data": {
                        "type": "roles",
                        "id": "moderator"
                    },
                    "links": {
                        "self": "/api/v1/users/1/relationships/role",
                        "related": "/api/v1/users/1/role"
                    }
                },
            }
        }
    ]
}

what i want to achieve is to have a attribute says

"enable-email-notification": true,

in json-api output

this column is created in pivot table "projects_members". a pivot table joining table "projects" and table "users".

the "enable_email_notification" is a field in table "projects_members".

[neomerx]

I think I understand you. It doesn't work this way because you want to add an extra column either to a project or to a user. But it's a bad idea because in some responses your project/users will have different columns and even worse that value for "enable-email-notification" in user depends on the project it refers to. What you really need is to have a separate model for project notification. It's not just a 'pivot' table in your case. It's a separate logical entity.

[neomerx]

I would just have a separate table project_notifications with columns

• project_notification_id
• project_id
• user_id
• timestamps

If there is a record in this table then it means "enable-email-notification": true, if not then false. Then it will be just a pivot table and you can have project->notifications relationship to users and no extra JSON API entity needed.

[neomerx]

Adding and removing in a relationship is quite easy to add

in Routes it might look like

self::addInRelationship($routes, PostSchema::TYPE, PostSchema::REL_LINKS, PostsController::class, PostsController::METHOD_ADD_LINKS);
self::removeInRelationship($routes, PostSchema::TYPE, PostSchema::REL_LINKS, PostsController::class, PostsController::METHOD_DELETE_LINKS);

and in the controller

    public static function addLinks(
        array $routeParams,
        ContainerInterface $container,
        ServerRequestInterface $request
    ): ResponseInterface {
        $response = static::addInRelationship(
            $routeParams[static::ROUTE_KEY_INDEX],
            Scheme::REL_LINKS,
            Model::REL_LINKS,
            $container,
            $request
        );

        return $response;
    }

    public static function deleteLinks(
        array $routeParams,
        ContainerInterface $container,
        ServerRequestInterface $request
    ): ResponseInterface {
        $response = static::deleteInRelationship(
            $routeParams[static::ROUTE_KEY_INDEX],
            Scheme::REL_LINKS,
            Model::REL_LINKS,
            $container,
            $request
        );

        return $response;
    }

[dreamsbond]

Picked ur suggestion. Ya. Seperating table was right way. Works and thanks so much.

For addInRelationship, I am not quite understanding how it is used in together with separating table. Could you elaborate more?

[neomerx]

It was a continuation of my post about project_notifications where the row existence means that user should be notified. With predefined methods addInRelationship and deleteInRelationship you can easily add support for adding/removing elements from the relationship.

[dreamsbond]

Got it, thanks