Category : sql-order-by

I cant orderBy points. Points is accessor. Controller: $volunteers = $this->volunteerFilter(); $volunteers = $volunteers->orderBy(‘points’)->paginate(10); Volunteers Model: public function siteActivities() { return $this->belongsToMany(VolunteerEvent::class, ‘volunteer_event_user’, ‘volunteer_id’, ‘volunteer_event_id’) ->withPivot(‘data’, ‘point’, ‘point_reason’); } public function getPointsAttribute(){ $totalPoint = 0; $volunteerPoints = $this->siteActivities->pluck(‘pivot.point’, ‘id’)->toArray() ?? []; foreach ($volunteerPoints as $item) { $totalPoint += $item; } return $totalPoint; } But I ..

Read more

I have three table like this : tbl_product product_id users id user_product id product_id user_id It is a ManyToMany relationship. Models Product: Product_user(){ return $this->belongsToMany(User::class, ‘user_product’, ‘product_id’, ‘user_id’); } Models User: User_product(){ return $this->belongsToMany(Product::class, ‘user_product’, ‘user_id’, ‘product_id’); } Query select top like product by user: $toplike = Product::with(‘Product_user’) ->whereExists(function ($query){ $query->select(‘product_id’, DB::raw(‘count(product_id) as total’)) ->from(‘user_product’) ..

Read more

I have done searching for encrypted and non-encrypted columns but for ordering non-encrypted fields are ordering successfully but encrypted fields are not sorting. Encrypted columns: firstname, lastname, email Encrypt Function // Encrypt Data function encrypted_data($decrypted){ if($decrypted != NULL){ $method = ‘aes-256-cbc’; // Must be exact 32 chars (256 bit) $salt = ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’; $password = substr(hash(‘sha256’, ..

Read more

I have done searching for encrypted and non-encrypted columns but for ordering non-encrypted fields are ordering successfully but encrypted fields are not sorting. code like for searching if(isset($_POST[‘search’]) && isset($_POST[‘search’][‘value’]) && !empty($_POST[‘search’][‘value’])) { $search_keyword = trim($_POST[‘search’][‘value’]); $encrypted = encrypted_data($search_keyword); $search_query = "AND id like ‘%$search_keyword%’ OR pin_local_id like ‘%$search_keyword%’ OR firstname like ‘%$encrypted%’ OR lastname ..

Read more

I have to need to order by records based on withcount() function in laravel 6 $query=User::withCount([‘time_spent’ =>function($q){ $q->select(DB::raw(‘COALESCE(sum(COALESCE(time_spent, 0 )),0)’)) ->whereDate("created_at", ">=", Carbon::today()->startOfMonth()->toDateString()) ->whereDate("created_at", "<=", Carbon::today()->endOfMonth()->toDateString()); }]) ->withCount([‘interactive_time_spent’ =>function($q){ $q->select(DB::raw(‘COALESCE(sum(COALESCE(audio_video_time, 0 ) + COALESCE(chat_time,0)),0)’)) ->whereDate("created_at", ">=", Carbon::today()->startOfMonth()->toDateString()) ->whereDate("created_at", "<=", Carbon::today()->endOfMonth()->toDateString()); }]) ->orderBy("(interactive_time_spent_count + time_spent_count)",$sortOder)->get(); In this code, I have two withCount() functions and I need ..

Read more