Dg Dinesh Dg Dinesh - 1 year ago 82
MySQL Question

Convert my sql query to laravel query

I want to convert a normal sql to laravel query .Property is the model name . ..please let me know for this query to laravel model query

SELECT * FROM property WHERE
property.active = 1 AND
on_market = 1 AND
department = 'lettings' AND
price > 0 AND
( submitted_at IS NULL OR submitted_at = '0000-00-00 00:00:00' OR ( submitted_at IS NOT NULL AND approved_at IS NOT NULL ) )enter code here

Answer Source

Try this query, which uses a raw WHERE:

DB::table('property')
        ->select('*')
        ->where('active', '=', 1)
        ->where('on_market', '=', 1)
        ->where('department', '=', 'lettings')
        ->where('price', '>', 0)
        ->where(DB::raw("COALESCE(submitted_at, '0000-00-00 00:00:00') = '0000-00-00 00:00:00' OR approved_at IS NOT NULL"))
        ->get();

The second half of the OR in your final WHERE condition I think can be reduced to approved_at IS NOT NULL since submitted_at would already be not NULL in order for this condition to be considered.

Also, there is a code smell if you really expect submitted_at to have the value of 0000-00-00 00:00:00. Ideally, you would only have to check for NULL, but in any case my use of COALESCE() should match your intended logic.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download