Looking for Eloquent equivalent to this basic query using "DISTINCT"

replied 9 years ago

Solution

There's a couple of ways to do this.

Assuming you have a model called Picks that represents the picks table in your database, you can use it like so - it might be worth including the groupBy clause too.

$picks = Picks::distinct()->select('user_id')->where('weeknum', '=', 1)->groupBy('user_id')->get();

Or if you don't have a model, you can use the DB query builder in a similar way:

$picks = DB::table('picks')->distinct()->select('user_id')->where('weeknum', '=', 1)->groupBy('user_id')->get();

Or, if the distinct() doesn't work, you can force it via DB::raw:

$picks = DB::table('picks')->select( DB::raw('DISTINCT(user_id)') )->where('weeknum', '=', 1)->groupBy('user_id')->get();

Last updated 1 year ago.

0

jaystew

replied 9 years ago

Excellent! I do have a Picks model so the first example was what I was looking for.

Last updated 1 year ago.

0

goranata

replied 9 years ago

Any body might have an idea, how to use distinct(), in a search in radius query, .e.g using the haversine formula:

$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();

I've tried with ->groupBy, but it get me empty data.

Last updated 9 years ago.

0

thetitoo

replied 8 years ago

@goranata, I know it's off-topic, but if you want to use geoqueries, I warmly recommend to use Mongodb

0

apps4u

replied 8 years ago

     return $this->createModel()->select(
            DB::raw("`name`, `latitude` , `longitude` ,  ( 6371 * acos( cos( radians(?) ) * cos( radians( `latitude` ) ) * cos( radians( `longitude` ) - radians(?) ) + sin( radians(?) ) * sin( radians( `latitude` ) ) )) AS `distance`"))
            ->groupBy('name')
            ->havingRaw("distance < ?")
            ->orderBy("distance")
            ->setBindings([$latitude, $longitude, $latitude, $radius])
            ->get();

if using Sqlite add math functions to PDO object.

     $pdo = DB::connection('sqlite')->getPdo();
        $pdo->sqliteCreateFunction('cos', 'cos', 1);
        $pdo->sqliteCreateFunction('acos', 'acos', 1);
        $pdo->sqliteCreateFunction('sin', 'sin', 1);
        $pdo->sqliteCreateFunction('radians', 'deg2rad', 1);

0

acrunam

replied 7 years ago

Thank you so much @jayhealey

0

Moderators