inserting data from a crud from one table into another table

[prescriptionlifeline]

So I have a users table and a dues table. The dues table looks like this:

CREATE TABLE `dues` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `userId` bigint unsigned NOT NULL,
  `year` smallint DEFAULT NULL,
  `datePaid` date DEFAULT NULL,
  `amountPaid` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

So the idea is users have to pay dues annually and we log when they paid and what they paid in the dues table. So like for a given user you might have something like this:

INSERT INTO dues (userId, year, datePaid, amountPaid) VALUES
    (10, 2020, '2020-01-10', 24),
    (10, 2021, '2021-01-15', 24),
    (10, 2022, '2022-02-01', 24);

On the crud for the users table I want to have 2x fields - date('Y') . ' Dues: Amount Paid' and date('Y') . ' Dues: Date Paid'. ie. on the user crud page these fields are only for the current year.

My question is... how can I do this? Turns out you can add fields for columns that don't exist without issue. To simulate pulling the data from the DB I could add something like this to the array passed to CRUD::field():

 `'default' => Dues::where([
    'userId' => ???,
    'year' => date('Y')
])

But what would I put in for the ???

And then there's actually saving the data. How would I do that?

Laravel-Backpack/CRUD#51 (comment) mentions the storeCrud method. I suppose I could implement that and do something like this:

    public function storeCrud(StoreRequest $request = null)
    {
        $result = parent::storeCrud($request);
        // extract the {id} from $result and then do an UPDATE or INSERT into the dues table as appropriate
    }

But idk if this is the best approach.

I suppose another method would be to add three columns to my users table: duesYear, duesDatePaid and duesAmountPaid. I could then create an SQL UPDATE TRIGGER so that when any of those fields are updated it INSERTs or UPDATEs the dues table as appropriate. If I went that route I'd also prob need to create an SQL INSERT TRIGGER as well but, overall, I don't like this idea. The idea of putting business logic into SQL TRIGGERS seems inelegant.

Any ideas?

[jcastroa87]

Hello @prescriptionlifeline

This look like something relationship field can handle, in you case check pivot table docs you can add other attributes to this relation.

Cheers.

[prescriptionlifeline]

So I tried to use the relationship field but am now getting this error:

Call to undefined method App\Models\Dues::identifiableAttribute()

Here's my Dues model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Dues extends Model
{
    use HasFactory;

    /**
     * The attributes that are mass assignable.
     *
     * @var array<int, string>
     */
    protected $fillable = [
        'userId',
        'year',
        'datePaid',
        'amountPaid',
    ];
}

Here's the method in my User model that defines the relationship:

    public function currentDues()
    {
        return $this->hasOne(Dues::class, 'userId')->latestOfMany('year');
    }

And here's my field entry in the setupCreateOperation method of UserCrudController.php:

        CRUD::field([   // relationship
            'name' => 'currentDues', // the method on your model that defines the relationship
            'type' => 'relationship',
        ]);

Any ideas?

Thanks!

[prescriptionlifeline]

Never mind - I figured it out. I needed to use the following two traits:

use Backpack\CRUD\app\Models\Traits\HasIdentifiableAttribute;
use Backpack\CRUD\app\Models\Traits\CrudTrait;

I also needed to replace ->latestOfMany('year') with ->where('year', date('Y')).

Thanks!

[jcastroa87]

Im glad you find the solution. I will close this issue, but please feel free to re-open or create a new one if needed.

Cheers.