Click here to Skip to main content
15,906,329 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello,
I am writing a program which needs several queries. all these queries work on the same table joins. I mean, three tables should be joined, but after that, different "select" employ on the result in each query. I want not to repeat table joins. How can I do that?

now I wrote a function to join tables as follows:
protected function sales_marketer_customres_join(){

        return sales::Join('marketers', 'sales.marketer_id', '=', 'marketers.id')
           
            ->join('customers', 'sales.customer_id', '=', 'customers.id')
         
           ->whereIn(DB::raw("substr(sales.date, 5, 2)"),$this->month);
    }

then, for each query, I called this function. for example:
$this->sales_marketer_customres_join()
-> where('marketer_id','=',$marketer_id)
    ->select('customers.*','marketers.*','sales.*',DB::raw("SUM(sales) as sales"))
            ->groupBy('customers.area')
            ->orderBy('customers.area')
            ->get();


the problem is that, this joins repeats for each query.

What I have tried:

in order to solve the problem, I defined a variable and put the output of "sales_marketer_customres_join" function in it and used it in other queries. but, it seems that when the select part gets run, the content of the variable changes. same as follows:
protected $sales_marketer_customres_join;

then, I initialized it in the constructor:
$this->sales_marketer_customers_joins=$this->sales_marketer_customres_join();

but,it seems that the content of this variable changes after employing each "select" on it. so, for the next query it does not give correct answer.
Posted
Updated 3-Dec-22 2:17am
v2
Comments
Graeme_Grant 3-Dec-22 8:30am    
Use a view...
Paul Maxwell 2023 9-Mar-23 7:27am    
Why not create a view in the database containing those joins? Then you just have one object to call and can rely on it behave consistently. Once it is created just treat it like a table and filter the data as you would normally. Don't be fooled into using "select *" though - that is false economy.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900