Laravel - Model created by custom query-CodePudding

I'm new to laravel, and I have no idea how to search for this.

I do have the following structure:

table_a -> a_id, name, document;

table_b -> b_id, a_id, sys_id, com_id, deleted_at;

Mainly table_a will store the data of a "user" and table_b contains the relation of system/companys that have acces to the data published to that user.

I will always need the data like: a_id, name, document, []int sys, []int com;

To do so I would like to create a model that is "populated" with the following query:

select a.a_id, a.name, a.document, ARRAY_AGG(distinct(b.sys_id)) sys, ARRAY_AGG(b.com_id) com
from table_a a 
left join table_b b on a.a_id = b.a_id
     and b.deleted_at is null
group by 1,2,3

in a way that i can do: model->all() and model->where("document", 111)

Obs: I tried to do so using one to many relation, but i want to get the data in a single object.

Obs2: I thought about creating a custom class with distinct "constructors" in a way that one returns an array of it and the other one returns an object.

CodePudding user response：

Disclaimer: As mentioned by miken32 what I'm trying to do isn't the right way to use a model-view-controller framework as laravel.

That said the solution for what I was looking for indeed are the global scopes as suggested by Gui

CodePudding user response：

You can use laravel Query Builder, it would allow you to write sql queries, you import facade class: use Illuminate\Support\Facades\DB;

$result = DB::table('table_a')
          ->join('table_b', 'table_a.a_id', '=', 'table_b.a_id')
          ->select('table_a.a_id', 'table_a.name', 'table_a.document', DB::raw('avg(table_b.sys_id) as sys_id'), DB::raw('avg(table_b.com_id) as com_id') )
          ->where('table_b.delete_at', '=', null)
          ->groupBy('table_a.val')
          ->get();

You can modify this to suit your need.