Laravel5.1 query and subquery

Question

How can I improve this query:

SELECT * FROM forum_thread WHERE
    thread_id not in (
        SELECT auth_id FROM forum_auth WHERE auth_group_id=1 and auth_type=2 and auth_visible=0
    )
    and thread_id in (
            SELECT thread_id FROM forum_thread WHERE
            category_id NOT IN (
                    SELECT auth_id FROM forum_auth WHERE auth_group_id=1 and auth_visible=0 and auth_type=1
            )
    )
ORDER BY last_post_id DESC limit 30

Thanks for answer

This query is very complicated. I would like change it for something like that: ForumThread::whereNotIn('thread_id', {auth_id_array})->whereIn('thread_id', {thread_id_array})->orderBy('last_post_id', 'DESC')->limit(30)->get(); but i have a problem to create subquery in subquery — Pionas, Sep 08 '15 at 10:34

score 2 · Accepted Answer · edited May 23 '17 at 12:07

You can try this query for better performance:

SELECT * FROM forum_thread WHERE
thread_id NOT EXIST(
    SELECT auth_id FROM forum_auth WHERE auth_group_id=1 and auth_type=2 and auth_visible=0
)
and thread_id EXIST(
        SELECT thread_id FROM forum_thread WHERE
        category_id NOT EXIST(
                SELECT auth_id FROM forum_auth WHERE auth_group_id=1 and auth_visible=0 and auth_type=1
        )
)
ORDER BY last_post_id DESC limit 30

Just change NOT IN to NOT EXIST - see this link: NOT IN vs NOT EXISTS

and also change IN to EXIST - see also this link: Difference between EXISTS and IN in SQL?

Enjoy!

Thanks, i very helpful, but i still looking how change this query to laravel query builder — Pionas, Sep 08 '15 at 10:58

Laravel5.1 query and subquery

1 Answers1