0

In my query I need to couple my Workflow model to the currently logged in Client via two linked tables: ClientCompany and CompanyWorkflow.

Getting the base-query written down wasn't a problem:

DB::table('workflows AS w')
    ->leftJoin('company_workflow AS cw', 'cw.workflow_id', '=', 'w.id')
    ->leftJoin('client_company AS cc', 'cw.company_id', '=', 'cc.company_id')
    ->where([
        ['cc.client_id', '=', $this->id]
    ]);

This query won't be accepted as a relational query and I would like to use the following method $client->workflows to get the available set of workflows.

Is there a way I can either convert a query to a relational query or a way in which I can chain multiple belongsToMany queries?

My database structure is as such:

client
id

client_company
id    client_id    company_id

company
id

company_workflow
id    company_id   workflow_id

workflow
id

UPDATE: Currently I've been able to construct the following hasManyThrough which almost outputs the correct query:

    $this->hasManyThrough('\App\Workflow', '\App\CompanyWorkflow', 'workflow_id', 'id')
        ->leftJoin('client_company AS cc', 'company_workflow.company_id', '=', 'cc.company_id')
        ->where([
            ['cc.client_id', '=', $this->id]
        ]);

The above query is constructed as:

select * from `workflows` inner join `company_workflow` on `company_workflow`.`id` = `workflows`.`id` left join `client_company` as `cc` on `company_workflow`.`company_id` = `cc`.`company_id` where `company_workflow`.`workflow_id` = ? and (`cc`.`client_id` = ?)

But for some reason, I am still not getting any results. Direct execution in the database with the correct ids however DOES return the correct set.

Will update the question when I've found the answer.

BRO_THOM
  • 824
  • 9
  • 24
  • belongsToMany is a relationship to define, not part of the eloquent query builder from your answer. it is unclear what you would like to achieve. – online Thomas Jan 25 '19 at 13:41
  • I think we would be able to help better if you show us how you defined your current relationships and what do you want to do next. – Mozammil Jan 25 '19 at 13:42
  • @Mozammil and Thomas - I've expanded my answer by including the raw schema of my database. – BRO_THOM Jan 25 '19 at 13:47
  • 1
    I used this ages ago. Might help. https://github.com/znck/belongs-to-through – Tarek Adam Jan 25 '19 at 14:10

0 Answers0