Hello Community...

New guy here working on private project that is far simpler that my work scenario encounter... Please assist with the below

I have found myself to be privileged to work on a oracle database which has several schema's that have Tables which have columns related to each other but have no relationships (foreign keys). They are only indexed and have primary keys but the rest it would seem is programatically linked... Now this was in play well before my arrival and for my team I am looking to create a dashboard on the data with notifications etc...

I know at the end of this I will have learnt a lot but it seems a lot to me right now and I am feeling a little lost as to the direction I Should take

I find that each schema has its own authentication and while some accounts can read other schema's only that schema's account can write to it... So i have several issues...

1. I am unable to define relationships in the Models so using Eloquent relationships will not work in this case.

this is the original raw select statement that I have which joins 3 tables tested and working in its raw format

$results = DB::connection('oracle')
            ->table('table1')
                ->select('table1.source as Source', 'table2.cdc as CDC', 'table1.attr_4 as attr_4', 'table1.attr_24 as attr_24')
                ->join('table3', function($join)
                {
                    $join->on('substr(table1.input_attr_24, 27, 14)', '=', 'table3.reference_key')
                        ->on('table3.group_key', '=', 'substr(table1.attr_24, 6, 16)');
                })
                ->join('table1,'substr(table1.attr_24, 1, 5)', '=', 'table3.code')
                ->whereIn('table1.id',[8, 9, 10, 13, 17, 18, 41, 42, 43, 44, 45, 46, 47])
                ->where('table1.status', '=', 1)
                ->get();

The above example is only in a single schema

what i am trying to do is to change it so that I take advantage of eloquent models so that i would be able to do something like

$results = table1(Model)::select()
->join(table2(Model) on x) and ()
->join(table3(Model) on x) and ()
->get

so not specifying the connection but having the connection defined in the model?

So here are the things I have cooked up to this problem

• can just define the connection setting in the Model to define which account is used to authenticate for that model? Would this allow joins on models from two different schema's? or would i have to specify the connection in the query builder statement like so

<?php

class SomeModel extends Eloquent {

    protected $connection = 'schema2'; //(Will this work?)

    protected $table = 'schema.table1'; //(can you define the Schema.Table when you define the table the model should use?)

}

Worst case I dont think its a big issue to define the connection in the query builder as long as I use models and not direct sql to the database.

I have a Models folder with sub-folders for each schema to keep things manageable

• if I define the model's per schema would i be able to run joins across multiple schema's with different accounts or if i read from one account but need separate accounts to write then what?
• Can you use the schema.table like you would in a SQL Statement to define the table that should be used in the model?

2. where would I put my logic as I dont want to get the controllers over loaded so assuming i use the raw statement would i create a method in the model and then call that from the controller when i am using it?

• Problem with this is its referencing multiple models so i can put it in a single model unless its only related to that model.

Using my raw statement first provided where would i put that? in a method in the controller that executes ? or somewhere else?

3. I am also looking at the Repository route to resolve this but was hoping to get it working before i go that route as it seems more advanced and i am not fully familiar with it to be confident in using it... As i am still trying to wrap my head around the whole repo/interface/IoC and while i am getting there I still need time...

I was looking to get things working and then i could later just re-factor it using the Repository setup?

also Read a bit on multitenancy which is perfect for other cases and would work in this case but is it really necessary when there is a fixed amount of schema's (10-15)?

Any guidance would be appreciated.