Am Writing my own SQL Query to Fetch Data From Two Different Models and also i have some filters , since i was using Spatie Query Builder i just configure my filters in allowedFilters() method and everything is okay , but know am moving from Spatie to Raw Sql Query where i can manipulate each character of my query giving below 2 examples one using Spatie Query Builder and another using my Custom Query the question here is how to apply those filters into my SQL Query when they sent in the request

**Spatie Example **

$transactions = QueryBuilder::for(Transaction::class)
                        ->with(['payable' => function (MorphTo $morphTo) {
                            $morphTo->morphWith([
                                Reservation::class => ['unit', 'depositInsuranceTransactions' , 'withdrawInsuranceTransactions', 'creator', 'customer', 'customer.nationality', 'customer.highlight'],
                            ]);
                        }])
                        ->with(['wallet','creator','last_user_update'])
                        ->allowedFilters([
                            AllowedFilter::scope('by_number'),
                            AllowedFilter::scope('by_reservation_number'),
                            AllowedFilter::scope('by_customer_id_number'),
                            AllowedFilter::scope('by_unit_number'),
                            AllowedFilter::scope('by_creator'),
                            AllowedFilter::scope('by_payment_type'),
                            AllowedFilter::scope('by_date_from'),
                            AllowedFilter::scope('by_date_to'),
                            AllowedFilter::scope('by_term')
                        ])
                        ->allowedSorts([
                                'id',
                                '-id',
                                'created_at',
                                '-created_at'
                        ])
                        ->whereHasMorphIn('payable', ['App\\Team','App\\Reservation'], function ($query, $modelType) use($reservations_ids,$team_id , $type){

                            if ($modelType == 'App\\Team') {
                                $query->where('payable_type' , 'App\\Team')
                                ->where('payable_id',$team_id);
                            }

                            if ($modelType === 'App\\Reservation') {
                                $query->where('payable_type' , 'App\Reservation')
                                ->with('unit', 'depositInsuranceTransactions' , 'withdrawInsuranceTransactions' ,'customer','customer.nationality','customer.highlight')
                                ->whereIntegerInRaw('payable_id', $reservations_ids);
                            }
                        })
                        ->where('amount', '!=' , 0)
                        ->where('type' , '=' , $type)
                        ->where('is_public' , 1)
                        ->whereNull('deleted_at')
                        ->orderByDesc('number')
                        ->simplePaginate(20);

And This is My Custom Query

$transactions  =
    DB::select(
            DB::raw("SELECT tr.*, w.decimal_places
                    FROM transactions tr
                        JOIN wallets w ON w.id = tr.wallet_id
                    where
                    (
                    -- I Wanna Put Here Some Conditions When the filter x is not null apply it to the query
                    )
                    AND (
                            (
                                tr.payable_type = :team_base_class
                                AND tr.payable_id = :team_id
                                AND tr.amount != 0
                                AND tr.type = :team_transaction_type
                                AND tr.is_public = 1
                                AND tr.deleted_at IS NULL
                            )
                            OR (
                                    tr.payable_type = :reservation_base_class
                                AND tr.payable_id IN ( $implodeded_reservations_ids 
                            )
                            AND tr.amount != 0
                            AND tr.type = :reservation_transaction_type
                            AND tr.is_public = 1
                            AND tr.deleted_at IS NULL
                        )
                    )

                    ORDER BY tr.number DESC
            ") // End of DB RAW

            , array(
                'team_base_class' => 'App\\Team',
                'reservation_base_class' => 'App\\Reservation',
                'team_id' => $team_id,
                'team_transaction_type' => $type,
                'reservation_transaction_type' => $type
            )
    ); // End of DB SELECT

**My Filters Coming From Request **

{
"by_number":"129",
"by_reservation_number":"120",
"by_date_from":"2022-02-01 12:00",
"by_date_to":"2022-02-11 12:00",
"by_payment_type":"cash",
"by_term":"36708"
}

I Tried So Many Times Applying IF condition inside where but always getting syntax error and i don't know if what am doing right or wrong.

Why Am Moving To Custom Query Rather Than Spatie

Well am looking for the most optimized performance i can get and i really found that direct SQL Queries are so fast rather than any extra tooling like spatie or even eloquent

CodePudding user response：

The query you're trying to do doesn't seem to match the raw sql below.

As for the conditionals, if you used the query builder you could use its when method.

$transactions = DB::query()
    ->select('tr.*', 'w.decimal_places')
    ->from('transactions', 'tr'
    ->join('wallets w', 'w.id', 'tr.wallet_id')
    ->where(function ($query) use ($filters) {
        $query->when(isset($filters['a']), function ($query) use ($filters) { $query->where('a', $filters['a']); })
              ->when(isset($filters['b']), function ($query) use ($filters) { $query->where('b', $filters['b']); })
              ->when(isset($filters['c']), function ($query) use ($filters) { $query->where('c', $filters['c']); })
              ->when(isset($filters['d']), function ($query) use ($filters) { $query->where('d', $filters['d']); })
              ->when(isset($filters['e']), function ($query) use ($filters) { $query->where('e', $filters['e']); });
    })
    ->where(function ($query) use ($team_id, $type) {
        $query->where(function ($query) use ($team_id, $type) {
            $query->where('tr.payable_type', 'App\\Team')
                ->where('tr.payable_id', $team_id)
                ->where('tr.amount', '!=',  0)
                ->where('tr.type', $type)
                ->where('tr.is_public', 1)
                ->whereNull('tr.deleted')
        })
        ->orWhere(function ($query) use ($reservations_ids_array, $type) {
            $query->where('tr.payable_type', 'App\\Reservation')
                ->whereIn('tr.payable_id', $reservations_ids_array)
                ->where('tr.amount', '!=', 0)
                ->where('tr.type', $type)
                ->where('tr.is_public', 1)
                ->whereNull('tr.deleted_at')
        });
    })
    ->orderByDesc('tr.number')
    ->get();