Laravel Eloquent "WHERE NOT IN"

📚 Laravel Eloquent "WHERE NOT IN" 🚫

Are you having trouble writing queries in Laravel Eloquent ORM? Don't worry, we've got you covered! In this blog post, we'll walk you through converting a common SQL query into Laravel Eloquent, specifically focusing on the "WHERE NOT IN" clause.

The Challenge 😰

One of our readers faced a challenge when trying to convert the following SQL query into Laravel Eloquent:

SELECT book_name, dt_of_pub, pub_lang, no_page, book_price  
FROM book_mast        
WHERE book_price NOT IN (100, 200);

The goal was to retrieve book details excluding those with a book price of 100 or 200.

The Solution 💡

To achieve the same result using Laravel Eloquent, you can leverage the whereNotIn method. Let's see how it works:

$books = DB::table('book_mast')
           ->select('book_name', 'dt_of_pub', 'pub_lang', 'no_page', 'book_price')
           ->whereNotIn('book_price', [100, 200])
           ->get();

In the above code snippet, we use the DB facade to access the table book_mast. Next, we select the desired columns using the select method. Finally, we apply the whereNotIn method to exclude books with a price of 100 or 200.

🎉 Pro Tip: Use Eloquent Models 🏆

While the above solution works perfectly fine, we always recommend using Eloquent models for a more expressive and object-oriented approach. Here's how you can achieve the same result using an Eloquent model:

$books = BookMast::select('book_name', 'dt_of_pub', 'pub_lang', 'no_page', 'book_price')
                 ->whereNotIn('book_price', [100, 200])
                 ->get();

By defining a BookMast Eloquent model and using it in your query, your code becomes more elegant and readable.

Conclusion 🎓

In this blog post, we demonstrated how to convert a SQL query with a "WHERE NOT IN" clause into Laravel Eloquent. We covered both the basic solution using the DB facade and the recommended approach using Eloquent models.

Now that you have learned this handy technique, try applying it to your own projects and simplify your queries. Happy coding! 😊

Have any questions or suggestions? Leave a comment below and let's discuss! 👇