Change the query to coltroller Laravel project-CodePudding

I discovered that the dev team on my project (Laravel 7) writes a database query that takes a long time to return results, I monitor mysql_slow.log and the other controller generates the following query:

select `id`, `name`, `slug`, `source_id`, `status`, `author_id`, `total_words`, `view`, `cover`, `desc`, (select count(*) from `transactions` where `stories`.`id` = `transactions`.`story_id`) as `transactions_count` from `stories` where `display` = 1 
and exists (select * from `categories` inner join `story_categories` on `categories`.`id` = `story_categories`.`category_id` 
where `stories`.`id` = `story_categories`.`story_id` and `category_id` in (1, 2, 5, 9, 16, 81))
order by `transactions_count` desc limit 10;

It is called from the controller:

// Story suggestion
    public function stSuggestion(Request $request, Story $story)
    {
        $categories = $story->categories;
        $categories = $categories->map(function ($item) {
            return   $item->id;
        });
        $stories = Story::select(['id', 'name', 'slug', 'source_id', 'status', 'author_id', 'total_words', 'view', 'cover', 'desc'])->with('author:id,name,slug')->withCount('transactions')
            ->display()
            ->orderBy('transactions_count', 'desc')->whereHas('categories', function (Builder $query) use ($categories) {
                $query->whereIn('category_id', $categories);
            })->take(10)->get();
        return response()->json($stories);
    }

And I tried that query with phpMyAdmin and found its response time extremely slow (58s). Thereby I realized that my site loads very slowly because of that query. I realized the problem that the query was written not optimally, it counted too many times so I rewrote another query. I modified the query as follows:

select STR.`id`, `name`, `slug`, `source_id`, `status`, `author_id`, `total_words`, `view`, `cover`, `desc`, STC.Transaction_Count    
from `stories` as STR
         Inner Join (Select `story_id`, count(`story_id`) as 'Transaction_Count' From transactions Group By `story_id`) 
         STC On STC.`story_id`= STR.`id`
         Inner Join story_categories CAT On CAT.`story_id` = STR.`id`
         And CAT.category_id In (.....)
where STR.`display` = .... 
order by STC.Transaction_Count desc limit 10;

I tried running the command on phpMyAdmin and the response time was 0.5s (too perfect compared to 58s above). My problem is that I don't know how to convert my rewritten query into Controller. Please help me! My project was abandoned by the dev team :(

CodePudding user response：

You shouldnt even be attempting to rewrite your query in controller. simply use DB::raw() to run the raw query in the controller. you will get the same data. An example

$process_history = DB::select(DB::raw("select `process_list_hist`.`desk_id`,`as`.`status_name`,
                            `process_list_hist`.`process_id`, 
                            if(`process_list_hist`.`desk_id`=0,\"-\",`ud`.`desk_name`) `deskname`,
                            `users`.`user_full_name`, 
                            `process_list_hist`.`updated_by`,
                            group_concat(`pd`.`file`) as files
                            from `process_list_hist`
                            left join `user_desk` as `ud` on `process_list_hist`.`desk_id` = `ud`.`desk_id`
                            and `process_list_hist`.`status_id` != -1
                group by `process_list_hist`.`record_id`,`process_list_hist`.`desk_id`, `process_list_hist`.`status_id`
                order by process_list_hist.created_at desc
                "));

You can check it in detail here: https://laraveldaily.com/select-with-dbraw-make-your-database-work/

CodePudding user response：

I modified the controller as follows:

// Story suggestion
    public function stSuggestion(Request $request, Story $story)
    {
        
        $categories = $story->categories;
        $categories = $categories->map(function ($item) {
            return   $item->id;
        });
        $stories = DB::table('stories as STR')
        ->select (STR.'id', 'name', 'slug', 'source_id', 'status', 'author_id', 'total_words', 'view', 'cover', 'desc',STC.transaction_count)
        ->join ( DB::raw('(Select 'story_id', count('story_id') as 'transaction_count' From transactions Group By 'story_id')  STC'), 
        function($join)
        {
             $join->on(STC.'story_id','=', STR.'id');
        })
        ->jon (DB:raw ('(story_categories) CAT'),
        function($join)
        {
             $join->on(CAT.'story_id' = STR.'id');
        })
        ->orWhere ('categories', function (Builder $query) use ($categories) {
        $query->whereIn('category_id', $categories);})
        ->display()
        ->take(10)->get();
        return response()->json($stories);      
                
    }

But Laravel shows error:

local.ERROR: syntax error, unexpected 'story_id' (T_STRING), expecting ')' {"exception":"[object] (ParseError(code: 0): syntax error, unexpected 'story_id' (T_STRING), expecting ')

enter code here