How to create an eloquent model with a database function as source?

Question

Some background information:

The Model to create is for an existing partitioned table in a postgresql database. Cause of performance issues it is not ideal to request the parent table. We created a custom database function to get the data from the child tables and connect the result by union all depending on a parameter. The data for the parameter is defined by the user permission, so the ids are known when the Model is created. The logic behind relies heavily on Models and relation so raw queries would cause big trouble.

Currently I use the parent-table to get the data but the request take 2-3 Seconds. When the child tables are called, the result is below 100 ms. So I need a possibility to get data from multiple the child-tables.

To the question:

How to set the existing function as base for the Model?

I've tried to put the function name in DB::raw, but the function is still escaped: SELECT *FROM "partionedTableTestGetData(1,2,3)". I need the function used as data source for the model. The function queries the child tables direct and uses a union all to build the result set.

Writing to this model isn't needed.

use Illuminate\Support\Facades\DB;

public static function boot()
{
    $oUser = Auth::user();
    $aIds = $oUser->getCustomIds();
    //some sanitation here
    $this->table = DB::raw('partionedTableTestGetData('.implode(',', $aIds ).')';
    static::addGlobalScope(new CustomScope('id-field'));
}

Database schema of the table:

- public.parent_table -> parent table
- parent_table.parent_table_123 -> child table of parent_table with all data of company with id 123
- parent_table.parent_table_321 -> child table of parent_table with all data of company with id 321
... about 600 other tables like  this ...

The following function is used to fetch the child_table data:

create function partionedTableTestGetData(companyids text) returns SETOF parent_table
    language plpgsql
as $$
DECLARE
  aiIDs BIGINT[];
  iID BIGINT;
  sSql TEXT;
  sSqlCondition TEXT;
  bFirst BOOLEAN;
  bParent BOOLEAN;
BEGIN
  bFirst = TRUE ;
  bParent = FALSE ;
  aiIDs = string_to_array(sSzenarioIDs, ',');
  sSql = '';
  sSqlCondition = '';

  FOREACH iID IN ARRAY aiIDs
  LOOP
    IF EXISTS (SELECT table_name FROM information_schema.tables WHERE table_name='parent_table' || iID AND table_schema = 'parent_table')
    THEN
      IF bFirst IS FALSE
      THEN
        sSql = sSql || ' UNION ALL ';
      END IF;
      bFirst = FALSE;
      sSql = sSql || format('SELECT * FROM parent_table.parent_table_%s %s', iID, sSqlCondition);
    ELSE bParent = TRUE;
    END IF;
  END LOOP;

  IF bParent IS TRUE OR sSql = ''
  THEN
    IF bFirst IS FALSE
    THEN
      sSql = sSql || ' UNION ALL ';
    END IF;
    bFirst = FALSE;
    sSql = sSql || format('SELECT * FROM ONLY public.parent_table %s', sSqlCondition);
  END IF;
  RAISE NOTICE '%', sSql;

  IF sSql <> ''
  THEN RETURN QUERY EXECUTE sSql;
  END IF;

  RETURN;
END;
$$;

user_12089257 · Answer

Although it feels like the cache tables are the preferred way of handling this issue. This should solve your problem.

$items = \DB::connection()->select(
      'SELECT * FROM partionedTableTestGetData(:ids)',
      ['ids' => implode(',', $aIds)]
);

If you would want a Eloquent model to be hydrated by the data that was fetched via the query you could use something like this:

Model::hydrate($items)->all() // returns Model[]

How to create an eloquent model with a database function as source?

1 Answers