limoncello-php
how would i access the extra column of the pivot table?
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?
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.
So I will have to add a new (viruual) field in model and attribute in scheme?
How do achieve this? Like virtual password?
could you provide a working sample of accessing extra column in pivot table?
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,
]),
]);
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;
}
got it, but i don't understand the linkage, that is how it can be accessed as an attribute in schema at the end.
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);
}
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.
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.
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.
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".
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.
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.
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;
}
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?
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.