I want to process data from database without collection

@jlrdw But I need to shift the projects as promotion on the top not on its origin place for Example when projects are displayed, two are displayed as a regular and the third is a promoter important I want to handle that with the query and this is my code to getting the point

        $queryProjects = Project::
            ->selectRaw($selectRaw)
            ->where([['verified', true], ['active', true]])
               ->orderBy('id', 'DESC');

        $allProjects                = $queryProjects->get();
        $promotedProjectsCollection = collect($queryProjects->promoted()->get());

        $promotedProjectsCollection = $promotedProjectsCollection->keyBy('id');

        $isAllPromoted = $allProjects->every(function ($project) use ($promotedProjectsCollection) {
            return $promotedProjectsCollection->contains('id', $project->id);
        });

        if ($isAllPromoted && ! $allProjects->isEmpty()) {

            $allProjectsWithPromoted = $allProjects;
        } else {

            $filteredProjects = $allProjects->reject(function ($project) use ($promotedProjectsCollection) {
                return $promotedProjectsCollection->contains('id', $project->id);
            });

            $i                       = 0;
            $allProjectsWithPromoted = [];
            foreach ($filteredProjects as $project) {
                $allProjectsWithPromoted[] = $project;
                $i++;
                if ($i % 2 == 0 && ! $promotedProjectsCollection->isEmpty()) {
                    $allProjectsWithPromoted[] = $promotedProjectsCollection->shift();
                }
            }
        }

        $collection     = collect($allProjectsWithPromoted);
        $perPage        = 20;
        $currentPage    = request()->input('page', 1);
        $paginatedItems = $collection->slice(($currentPage - 1) * $perPage, $perPage)->values();

        $paginator = new \Illuminate\Pagination\LengthAwarePaginator(
            $paginatedItems,
            $collection->count(),
            $perPage,
            $currentPage,
            [
                'path'  => request()->url(),
                'query' => request()->query(),
            ]
        );

@salahaldain-abduljalil If I understand you correctly, you want to set override the order by and prioritize promoted projects.

You can easily handle that with a regular subquery.

Something like.

SELECT *, (SELECT COUNT(*) FROM promoted_projects WHERE project_id = p.id) prio
FROM projects p
ORDER (SELECT COUNT(*) FROM promoted_projects WHERE project_id = p.id) DESC, p.title;

@Tray2 What I want is to arrange the projects in the form of two new, unpromoted projects and the third promoted, meaning all the newer projects are next to the newer ones, but after every two, the third is promoted, regardless of whether its ID is new or old means like this unpromoted unpromoted promoted unpromoted unpromoted promoted and so on.

@salahaldain-abduljalil You can setup a counter to capture every third result. This way you can stilll paginate results.

@salahaldain-abduljalil That is tricky to do with SQL, I would suggest looking into stored procedures, and use that to fetch the data you need.

@Tray2 Everything goes well with my code By collections and shift all promoted projects for Entire project But I need someway to change it via query to make it more Efficient , For Example in another databases like oracle etc.. You can do this via the row_number function but it is not supported inside mysql.

@jlrdw How do You mean can I see full Example please?

@salahaldain-abduljalil

Lookup the loop in the blade chapter

Something like:

@if($loop->iteration % 3 == 0)

Something like that, not tested.

See https://laravel.com/docs/12.x/blade#the-loop-variable

Or setup your own counter something like:

Just quick pseudocode:

$count = 1; 

foreach ($whatever as $key) {
    if ($count % 3 === 0) {
        do whatever;
    }else{
        do other thing
    $count++;
}

And paginate. Work out keeping track of the next page of data with count. You may need to pass current back to controller in the query string and even initialize it in the controller to maintain your every third item.

I normally just use a little trial and error til I figure out this type of thing.

@jaseofspades88 I agree a collection is fine for a smaller amount of data.

However I wouldn't put tens of thousands of records in a collection.

But working with a smaller query result, then yes collections can be very helpful.

I only setup things to query what's needed.

I have seen replies where someone starts using a collection with say a couple hundred records. Later as the data grows they see first hand that regular queries paginated is much better.

But @salahaldain-abduljalil mentioned all data. That's why I suggested only get what's needed. Then a collection would or could work well.