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
– Pionas, Commented Sep 8, 2015 at 10:34

Community · Accepted Answer · 2017-05-23 12:07:25Z

2

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!

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Sep 8, 2015 at 10:35

Makudex

1,0826 gold badges16 silver badges42 bronze badges

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

1 Comment

Pionas Over a year ago

Thanks, i very helpful, but i still looking how change this query to laravel query builder

Collectives™ on Stack Overflow

Laravel5.1 query and subquery

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related