Eloquent and relationship to a pivot table

Hi!

I have an event entrance system with a complex custom fields saved in the database.

The tables are (simplified):

accreditations: (saves a record when a person enters a conference) id person_id

fieldvalues: (Permitted values for a certain custom field) id field_id value

fields: (Custom field) id name

fieldvalue_person: (pivot table for storing the custom field value per person) id person_id fieldvalue_id

I have a working query that retrieves how many persons enter the event gruped by category. It's easy to generate it in Query Builder, but I want to know if it's possible to generate it in Eloquent.

Let's say we have this data:

accreditations: id person_id

1 124 2 123 3 94

fields: id name

1 category 2 status

fieldvalues: id field_id value 1 1 Electrical 2 1 Mining 3 1 Construction

fieldvalue_person: id person_id fieldvalue_id

1 124 1 2 123 1 3 94 2

And this is the working query

SELECT COUNT(fieldvalues.value) AS quantity, fieldvalues.value FROM accreditations LEFT JOIN fieldvalue_person on accreditations.person_id = fieldvalue_person.person_id LEFT JOIN fieldvalues ON fieldvalue_person.fieldvalue_id = fieldvalues.id WHERE fieldvalues.field_id = 1 GROUP BY fieldvalues.value ORDER BY value

The data returned is:

quantity value 2 Electrical 1 Mining

The actual Question is How do I translate this into Eloquent?

As far I understand, I will need to set a one to many relationship between accreditations and fieldvalue_person which right now is a Pivot Table.

Is that possible? Do I have to review the database design?

Sorry I don't know how to format the post.

Thanks.

faridbabayev

6 years ago

Level 2

i think you can use query buildier for best performance. Because you have many relations. You can translate query to query buildier like this.

DB::table('accreditations as AC')
    ->leftJoin('fieldvalue_person as FP',['AC.person_id' => 'FP.person_id'])
        ->leftJoin('fieldvalues as FV',['FV.id' => 'FP.fieldvalue_id'])
        ->where('FV.field_id',1)
        ->groupBy('FV.value')
        ->orderBy('FV.value')
        ->get();

AleSanchez

6 years ago

Level 1

Thank you for your response, despite the ugly post formatting. Can you tell me how I format the code on the post?

Please or to participate in this conversation.