## Laravel Eloquent Querying

These are some fundamental Eloquent queries as well as some complex ones that I always tend to look up...

How to use offset and limit during query
``````
// SELECT * FROM `table` offset 5 limit 10
\$blogs = MangoBlog::where('status', 'A')->orderBy('created_at')->skip(5)->take(10)->get();
``````
``````
// get the 2nd record
\$blogs = MangoBlog::where('status', 'A')->orderBy('created_at')->skip(1)->first();
``````

Nested Where SQL Clauses
Nested "Where Clauses" can be achieved in Laravel Eloquent by using a constraint as follows
``````
// SELECT * FROM `table` WHERE `x` AND (`y` OR `z`)
\$blogs = MangoBlog::where('status', 'A')->where(function (\$query) use (\$name) {
\$query->where('title', 'like', '%' . trim(\$q) . '%')
->orWhere('content', 'like', '%' . trim(\$q) . '%');
});
``````
``````
// SELECT * FROM `table` WHERE `x` OR (`y` AND `z`)
\$blogs = MangoBlog::where('status', 'A')->orWhere(function (\$query) use (\$name, \$age) {
\$query->where('name', \$name)->where('age', '>=', \$age);
});
``````

Where Exists SQL Clauses
``````
// SELECT * FROM `blog` WHERE exists (select 1 from `comments` where comments.blog_id = blog.id and comments.user_id = current user and status > 0)
\$blogs = MangoBlog::whereHas('comments', function (\$query) use (\$user)  {
\$query->where('user_id', \$user->id)
->where('status', '>=', 0);
})->get();;
``````

Where In SQL Clause
The "Where In" clause can be achieved in Laravel Eloquent by using an array
``````
// SELECT * from `table` where category_id in ('kids', 'mango', house')
\$blogs = MangoBlog::whereIn('category_id', array('kids', 'mango', 'house')->get();

// here's a way to covert the query result to an array so it can be used in WhereIn clause
\$blog_ids = MangoBlog::where('sticky', true)->lists('id');
\$blogs = MangoBlog::whereIn('id', \$blog_ids)->get();
``````

Query involving NULL in the where clause
``````
// SELECT * from `table` where category_id is null
\$blogs = MangoBlog::whereNull('category_id')->get();

// SELECT * from `table` where category_id is NOTnull
\$blogs = MangoBlog::whereNotNull('category_id')->get();
``````

Randomly select 5 comments (child records) for a given blog article
``````
// SELECT * from `table` order by RAND() limit 5
``````

Child/related records can be eager loaded as long as the relationship is defined in the model
``````
``````
``````
\$blogs = MangoBlog::with(array(
\$query->whereNotNull('verified_date')->orderBy('comment_date', 'desc');
}
))->orderBy('published', 'desc')->limit(5)->get();
``````

Name : Comment : view emoticons
Please consider signing up for our website.
{"ft_lang_label":"__label__en","ft_lang_prob":0.653606,"math_prob":0.5407774,"size":2928,"snap":"2020-45-2020-50","text_gpt3_token_len":781,"char_repetition_ratio":0.15458277,"word_repetition_ratio":0.07611549,"special_character_ratio":0.31420764,"punctuation_ratio":0.16504854,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9705922,"pos_list":[0,1,2],"im_url_duplicate_count":[null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-10-26T00:29:05Z\",\"WARC-Record-ID\":\"<urn:uuid:05c9926e-333e-4a9a-9b99-6a7ca57c2d66>\",\"Content-Length\":\"18099\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:bcd7171f-e138-4e12-a740-7164e0497d6b>\",\"WARC-Concurrent-To\":\"<urn:uuid:e53c66ef-0c7a-40ca-8871-6a3ddcb58401>\",\"WARC-IP-Address\":\"192.190.82.126\",\"WARC-Target-URI\":\"https://handymango.com/blog/community/laravel-eloquent-querying\",\"WARC-Payload-Digest\":\"sha1:7HRR4R6E6J536NEMAON6VNKVU5SV7R3A\",\"WARC-Block-Digest\":\"sha1:D5PAL7RLF6EGR2YEZYLKNVWVERALIKLN\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-45/CC-MAIN-2020-45_segments_1603107890108.60_warc_CC-MAIN-20201026002022-20201026032022-00309.warc.gz\"}"}