Updated April 11, 2023
Introduction to Laravel Distinct
Laravel Database Query Builder or LDQB is an easy to use interface and is used to run database queries. It is compatible with most database systems and supports all database operations. One of the pertinent features of the Query Builder is that it is based on the PDO parameter. The advantage of this is it safeguards the application against any malicious SQL injection attacks. Part of this syntax is Laravel Distinct. What this query does is picks the absolute relevant data that you have been searching for and returns the same as the value. So for example if I have to search for a particular hotel type within a particular radius, the Distinct query is going to look something like this:
$hCat = Hotels::select(
DB::raw('DISTINCT(hoteltype_id)'), // hotel category
DB::raw('6371 * acos( cos( radians(' . $airport->latitude.') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('. $airport->longitude .') ) + sin( radians('. $airport->latitude . ') ) * sin( radians( latitude ) ) ) AS distance'))
->having('distance', '<', $radius);
$hCategories = $hCat->distinct('hoteltype_id')->get();What is Laravel Distinct?
As mentioned in the previous paragraph, the Laravel statement of DISTINCT is a query that identifies the exact value of the search. It returns the value from the database through the following Eloquent Distinct method:
DB::table('tablename')->distinct('name')->count('name');How does Laravel Distinct Work?
One has to be careful while working with Laravel DISTINCT because it may not always provide you with the exact return. A turn around for that would be using the GROUPBY option, which essentially groups all the relevant search results and returns the same. One of the drawbacks of using the DISTINCT query is that if we are to request for multiple rows containing similar data that has to be merged together for the final output, the resultant will not be what we would expect it to be. Hence, under these circumstances, as mentioned previously we would be using the GROUPBY query.
For Example:
The table structure looks like this:
And I would want to have an output similar to this:
The Laravel Query Builder that has to be used for such an event would be:
DB::table('order')->select('date', 'seller', DB::raw( 'SUM(total) as total'))
->groupBy('seller')
->groupBy('date')
->get();However, for searches where multiple merges do not take place, the DISTINCT query works perfectly fine.
Hence, the important thing to remember here is the difference between GROUPBY and DISTINCT queries. Both return values, albeit, differently. The SELECT DISCTINCT query is used to return values which are different from each other.
The SELECT GROUP BY query is used to find values that are the same across multiple rows. Also, the GROUP BY query is used in conjunction with other functions like (COUNT, MAX, MIN, SUM, AVG), whereas, the DISTINCT QUERY is used as a standalone function.
Examples of Laravel Distinct
A few examples using the Laravel DISTINCT query to ease the understanding:
1. Want to run a query which is going to return a value with No Duplicates.
The SQL query is going to be:
SELECT DISTINCT column_name
FROM table_name;
SELECT DISTINCT 'ad_advertiser' FROM 'adverts'
The LARAVEL ELOQUENT query would be
MyModel::distinct()->get(['column_name']);2. If I have to return only value from a single column of the database table. The SQL query would be
SELECT DISTINCT 'ad_advertiser' FROM 'adverts'The LARAVEL ELOQUENT query would be
$query = DB::table('adverts')->distinct()->get(['ad_advertiser']);3. A trickier example would be to display value ignoring any null values. The SQL query would be
SELCT DISTINCT meta_value
FROM 'wp_postmeta'The LARAVEL ELOQUENT query would be.
Meta::distinct()->whereNotNull('meta_value')->get(['meta_value']);4. A more advanced version of the DISTINCT query would be one where you would want to fetch data from separate columns while still maintaining a value from the column to be distinct.
SELECT
Products.*
FROM
------ Get all the distinct product_id’s of ordered products.
------ (i.e. only show each product once, regardless of how many
------ times it has been ordered.
(SELCT DISTINCT product_id FROM orders) AS purchased_products
----- join back onto product table to show the full details of only
----- those products that have been ordered.
INNER JOIN products ON purchased_products.product_id = products.products_idWhat this does is identifies the values that need to be queried and returns them as asked. Please note that in this case an additional functionality like INNER JOIN.
5. A simpler example related to the DISTINCT query would be:
$picks = Picks::distinct()->select('user_id')->where('weeknum', '=', 1)->groupBy('user_id')->get();The similar SQL query to this would be:
SELECT DISTINCT user_id FROM picks WHERE weeknum = 1Laravel unveiled a painless method of coding and simplified the logic. Hence particular to the DISTINCT query the bare syntax would ideally look like:
$users = DB::table('users')->distinct()->get();The main task of the DISTINCT query is to force the process to reveal unique or distinct values only. One of the syntax errors that many of us make while using the DISTINCT query is something similar to this:
$players = Round::where('round_id', $currentgame)->distinct('steamid')->get();It does not confirm to the above kernel.
The correct syntax would be:
$players = Round::where('round_id', $currentgame)->distinct()->get(['steamid']);A couple of other variations would be:
$players = Round::distinct()->where('round_id', $currentgame)->get(['steamid']);$players = Round::select('steamid')->where('round_id', $currentgame)->distinct()->get();Laravel 5 unveiled an addition of the UNIQUE query. This has a similar functionality to DISTINCT. Furthermore, it accepts the $Key argument which is optional. This restricts the items which are to be returned.
An example would be:
public function unique(
$key = null,
$strict = false
);Over the years the PHP LARAVEL framework has become one of the most trusted tools for web development. The reasons are plenty.
- Laravel is simpler to use. It also gives options to developers for similar functions. For example, DISTINCT can always be replaced by UNIQUE or in an advanced version by GROUP BY.
- It integrates with the mail services.
- The integration with the tool makes building web applications faster and smoother.
- Automation testing is one of the quickest in Laravel.
It is for these reasons, that the market is replete, with web applications based on the LARAVEL platform.
Conclusion
The DISTINCT query in the ELOQUENT LARAVEL framework is an important function. However, owing to a few shortcomings, this query limits its uses. LARAVEL though has quite a few other queries which does an equally commendable job, of returning distinct values. This does not demean the DISTINCT query by any means. It is an essential cog in the wheel.
Recommended Articles
We hope that this EDUCBA information on “Laravel Distinct” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
