How do I convert this raw SQL query into a Laravel Eloquent ORM query?-CodePudding

I've been trying to convert a raw sql query to a Laravel Eloquent ORM query and I'm stuck.

                SELECT *
                FROM   transaction_history
                                   WHERE  type = 'debit'
                                   AND 
                                   (
                                        description not in ('Premium Membership Balance Top Up', 'Sign Up Bonus')
                                        OR
                                        description in ('Premium Membership Balance Top Up', 'Sign Up Bonus') and extra_details is not null
                                   )

I just started working on a Laravel project a couple weeks ago..my brain cells have exceeded their capacity by this time of day...and I have no idea where to even start. Help would be much appreciated.

CodePudding user response：

Assuming you have a TransactionHistory eloquent model representing transaction_history table, you can try to use where clauses with logical parameter grouping to generate the desired query.

You can check the generated sql query by replacing get() with toSql() and then dumping the output dd($records);

$records = TransactionHistory::query()
    ->where('type', 'debit')
    ->where(function($query) {
        $query->whereNotIn(
            'description', 
            ['Premium Membership Balance Top Up', 'Sign Up Bonus']
        ->orWhere(function($query) {
            $query->whereIn(
                'description', 
                ['Premium Membership Balance Top Up', 'Sign Up Bonus']
            )
            ->whereNotNull('extra_details');
        });
    })
    ->get();

Laravel 9.x Docs - Queries Logical Grouping

CodePudding user response：

You need use Raw Expressions Query Builder in laravel eloquent ORM.

Visit laravel doc => Raw Expressions

CodePudding user response：

You can use the raw query builder and just paste your query.
But the best way should be to create Models (think as table) and link those models with relationships (like foreign key).
Also, to get started, this tool can helps you to convert your query to a more laravel friendly query