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...
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
<?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
Using my raw statement first provided where would i put that? in a method in the controller that executes ? or somewhere else?
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.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community