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();
Excellent! I do have a Picks model so the first example was what I was looking for.
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.
@goranata, I know it's off-topic, but if you want to use geoqueries, I warmly recommend to use Mongodb
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);
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community