Laravel Eloquent Query - How to return unique values using DISTINCT

Question

I'm new to Laravel and Eloquent and am trying to retrieve unique records, from 3 tables, namely cupboards, item_types, and a pivot cupboard_items. There is also a 4th location table.

Constructed a working query in MySQL as below (tested successfully on MySQL workbench). Tried using DB::raw('') and DB::statement(''), but my IDE gave a warning that 'No data sources are configured to run this SQL and provide advanced code assistance' and using DB::raw returned an empty object while DB::statement returned 'true'.

SELECT cupboards.cupboard_id, cupboards.name, cupboards.capacity, dist.item_type_id, item_types.name, locations.coordinates FROM cupboards INNER JOIN
(SELECT DISTINCT cupboard_items.item_type_id, cupboard_id FROM cupboard_items) AS dist
ON cupboards.cupboard_id = dist.cupboard_id
INNER JOIN item_types ON dist.item_type_id = item_types.item_type_id
INNER JOIN locations ON locations.location_id = cupboards.location_id;

Not sure whether my syntax is wrong, but I subsequently also tried to construct a Eloquent query

$result = DB::table('cupboards')
            ->select(DB::raw('cupboards.cupboard_id, cupboards.name, cupboards.capacity, dist.item_type_id, item_types.name, locations.coordinates'))
            ->join(DB::raw('SELECT DISTINCT cupboard_items.item_type_id, cupboard_id FROM cupboard_items'))
            ->join('item_types', 'dist.item_type_id', '=', 'cupboard.item_type_id')
            ->join('locations', 'locations.location_id', '=', 'cupboards.location_id')
            ->get();

But apparently I can't have DISTINCT in my DB::raw query statement, and am not sure whether my join is done correctly. Any advice on how to proceed from here is much appreciated!!! Thanks!

Find this answer usefull http://stackoverflow.com/a/29728129/3918473 — Gayan, Jan 12 '17 at 12:19

score 1 · Answer 1 · answered Jan 12 '17 at 13:46

replace below query :

    $result = DB::table('cupboards')
        ->select(DB::raw('cupboards.cupboard_id, cupboards.name, cupboards.capacity, dist.item_type_id, item_types.name, locations.coordinates'))
        ->join(DB::raw('SELECT DISTINCT cupboard_items.item_type_id, cupboard_id FROM cupboard_items'))
        ->join('item_types', 'dist.item_type_id', '=', 'cupboard.item_type_id')
        ->join('locations', 'locations.location_id', '=', 'cupboards.location_id')
        ->distinct()
        ->get();

dist is a reference to the inner SELECT statement, but how would you indicate this in a laravel query? — jlyh, Jan 13 '17 at 03:34

score 0 · Answer 2 · answered Jan 12 '17 at 11:15

I think you do not need this complex query, I hope you want to get the values from cupboard model, with the location. Define these relations in cupboards class,

Cupboard {

public function units()
{
    return $this->belongsToMany('cupboardItem', 'cupboard_items', 'cupboard_id', 'item_type_id');
}

public function location()
{
    return $this->hasOne('location', 'location_id', 'location_id');
}

}

Then do a,

Cupboard::get();

If you get some duplicate records,

Cupboard::select('cupboards.cupboard_id, cupboards.name, cupboards.capacity, dist.item_type_id, item_types.name, locations.coordinates')->distinct()->get();

I guess this is good advice in the general direction -> to have a fat Model and a skinny Controller. But i'm very unfamiliar with the syntax of calling Model methods, anyhow I guess I'm just going to have to bite the bullet and go through learning how to implement these things. Thanks anyway. — jlyh, Jan 13 '17 at 03:38

Laravel Eloquent Query - How to return unique values using DISTINCT

2 Answers2