How to use subquery in laravel and convert sql query to laravel

Question

actually i am working on some maintenance project where previous developer used core sql query but i want to convert in to larvel query.. Here is Sql query :-

$directories = DB::select('
        SELECT d.id, d.name, d.url_match, d.login_url, d.register_url, d.notes, d.logo, d.require_verification FROM `directories` d 
        WHERE d.id
        NOT IN (
            SELECT c.directory_id FROM citations c
            INNER JOIN directories d
            WHERE c.directory_id = d.id
            AND d.allow_citation = 1
            AND c.site_id = ' . $siteID .'
        )
        AND d.allow_citation = 1
        AND d.deleted_at IS NULL
        AND d.tier = '. $tier .'
        ORDER BY d.tier ASC         
    ');

I have tried to convert in laravel below:-

 $directories = Directories::select('id','name','url_match','login_url','register_url','notes',
                 'logo','require_verification')
                ->where(['allow_citation'=>1,'tier'=>$tier])
                ->whereNUll('deleted_at')->get();

Can someone help me. Thanks in advance

kunal · Accepted Answer · 2018-10-29 10:52:13Z

1

Finally i have done myself after googling out:-

$directories = Directories::select('id','name','url_match','login_url','register_url','notes','logo','require_verification')->whereNotIn('id', function($query) use($siteID){
        $query->select('citations.directory_id')
            ->from('citations')
            ->whereRaw('directories.id=citations.directory_id')
            ->where('directories.allow_citation', 1)
            ->where('citations.site_id', $siteID);
        })->where(['allow_citation'=>1,'tier'=>$tier])->whereNUll('deleted_at')->get();

edited Oct 29, 2018 at 10:52

answered Oct 28, 2018 at 14:32

kunal

2533 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use subquery in laravel and convert sql query to laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related