Change the query to Builder query

@Sinnbeck Thank you. My project was abandoned by the dev team. So now I'm trying to fix it :( I want to convert the following query to replace the query in Coltroller, but I don't know how :(

 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;```

Coltroller:
``` // 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);
	}```

@hoangvnn sorry to hear that. Did you try checking with a debugger like debugbar or clockwork. Clockwork might be easiest if it returns json as you can just visit the route directly

And did you try toBase?

->take(10)->toBase()->get();

I am also curious what this query does. It gets 10 rows, but there is no pagination?

@Sinnbeck Thank you! It is true that this statement only takes 10 values. 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;

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. My problem is that I don't know how to convert my rewritten query into Controller.

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;