CODEWITHSUNDEEP
phpsqllaraveleloquent
Colorscream
Colorscream

Reputation: 59

Dynamic column/attribute. Stuck. Need inspiration

i’m looking for a solution to a problem that I’m having for a while now. Maybe you can inspire me to do this better. I’m trying not to make a basic mistake in the planning process therefore I’m asking you for advice.

I’m having a Contact::model which has few fixed attributes like id etc. Additionally I would like to have different attributes created dynamically for the whole Contact::model. Some user will be given the functionality to add attributes like name, email, address to the whole model. I’ve dropped the idea of programmatically updating the table itself by creating/dropping columns (this would introduce different problems). As for now i've created two additional tables. One with the additional column names [Columns::model] and a pivot table to assign the value to a Contact::model and Column::model.

To list all contacts i’m preparing the ContactColumn table as array where the first key is the contact_id and the second is the column_id, therefore i get the value. This introduces the n+1 issue. This would not be that bad, but with this approach it will be extremely hard (or resource consuming) to order the contacts by dynamic column values, filtering, searching etc.

Can you somehow guide me to a better solution. How can i merge the contact collection with the values for given columns so it looks like it was a fixed table?

<table>
    <thead>
        <tr>
            <th>Fixed columns [i.e. ID]</th>
            @foreach ($columns as $column)
                <th>{{ $column->name }}</th>
            @endforeach
        </tr>
    </thead>
    <tbody>
        @foreach ($contacts as $contact)
            <tr>
                <td>{{ $contact->id }}</td>
                @foreach ($columns as $column)
                    <td>
                        @if (array_key_exists($column->id, $values[$contact->id]))
                            {{ $values[$contact->id][$column->id] }}
                        @endif
                    </td>
                @endforeach
            </tr>
        @endforeach
    </tbody>
</table>

And the $value array.

foreach (ColumnContact::all() as $pivot) {
    $values[$pivot->contact_id][$pivot->column_id] = $pivot->value;
}

return $values;

Edit: I've solved it like this

$this->contacts = Contact::when($this->dynamicColumnName, function($query) {
    $query->join('column_contact', function ($join) {
        $join->on('id', '=', 'column_contact.contact_id')
            ->where('column_contact.column_id', '=', $this->dynamicColumnName->id);
        })
        ->orderBy('value', $this->orderingDirection);
    })
    (...)
    ->paginate(self::PER_PAGE);

Upvotes: 0

Views: 89

Answers (1)

Suleman
Suleman

Reputation: 104

Apart from the fixed fields, add an extra JSON field in your schema called 'custom_fields'. Have a look into => https://github.com/stancl/virtualcolumn

Separate table for custom fields is not a good idea because then you have to handle model events separately and so on.

Upvotes: 0

Related Questions