confusing on table relations

confusing on table relations

williewillie Posts: 4Questions: 2Answers: 0
edited July 2019 in Free community support

I have 5 tables want to link together, but little complex

companies
- id
- name

positions
- id
- name

company_position
- id
- company_id
- position_id

employees
- id
- name

employee_company_position
- id 
- employee_id
- company_position_id

Now, I need to create a CRUD page for Employee and bind to employee_company_position, I done it perfectly using Mjoin function

->join(
                Mjoin::inst('company_position')
                    ->link('employees.id', 'employee_company_position.employee_id')
                    ->link('company_position.id', 'employee_company_position.company_position_id')
                    ->fields(
                        Field::inst('id')
                            ->validator( 'Validate::required' )
                            ->options( Options::inst()
                                ->table('company_position')
                                ->where(function ($query) use($strCompanyIds){
                                    $query->where('company_id', $strCompanyIds,'IN',false);
                                })
                                ->value('id')
                                ->label(['company_id', 'position_id'])
                                ->order('company_id asc, position_id asc')
                                ->render( function($data) {
                                    $companyName = Company::where('id', $data['company_id'])->first()->name;
                                    $positionName = Position::where('id', $data['position_id'])->first()->name;
                                    $companyName = mb_substr($companyName, 0, mb_strlen($companyName) -4);
                                    return $companyName. ' -> ' . $positionName;
                                })
                            )
                    )
            )

and use the render function to display the count of linked company_position on datatable listing

data: null,
                className: "no_edit",
                sortable: false,
                searchable: false,
                render: function(data, type, row, meta) {
                    // console.log(data);
                    if (data.company_position.length !== 0) {
                        // console.log(data);
                        return '<a href="#">' + data.company_position.length+ '</a>';
                    } else {
                        return '<a href="#">No bind company_position </a>';
                    }
                }

but now I want to list out the company name and position name on the cell,
console.log (data) only show the company_position.id , not the rendered return return $companyName. ' -> ' . $positionName;

Thank you for helping :)

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    not the rendered return return $companyName. ' -> ' . $positionName;

    It wouldn't - that is only available in the options, not the data for the row.

    Are the company name and position name available in the company_position table? If so you can add them via additional Fields. If they aren't, then you might need to create a VIEW which will do a left join to build in that data, and point the Mjoin at that VIEW (since Mjoin doesn't currently support left joins).

    Allan

This discussion has been closed.