Debug School

rakesh kumar
rakesh kumar

Posted on • Updated on

Optimizing Sum Calculations in Laravel Queries for Influencers

Calculates the total sum of prices for each record based on specific conditions

Calculate the sum for each record is in associative array form after json decode
converting json format to associative record for single field
Add the sum to the response data or existing $data

Calculate the sum for each social site across all records is in associative array form after json decode
How to remove nonnumeric char to perform calculation

Calculates the total sum of prices for each record based on specific conditions 

 public function allinfluencerdata(Request $request)      
         {  

            log::info("data allinfluencerdata");
            $currentURL = url()->current(); 
            $login_email = Auth::user()->email;
            $profiles = Addprofile::all();           
            log::info("data if ke andar hai");  
            $data = DB::connection('payments')->table("social_url")
            ->leftJoin('countries', 'social_url.country_id', '=', 'countries.country_id')
            ->leftJoin('states', 'social_url.state_id', '=', 'states.state_id')
            ->leftJoin('addcarts', 'social_url.user_id', '=', 'addcarts.influencer_admin_id')
            ->leftJoin('cities', 'social_url.city_id', '=', 'cities.city_id')
            ->where('social_url.user_email', '<>', $login_email)
            ->select(
                'social_url.*',
                'addcarts.influencer_admin_id',
                'addcarts.cart_socials',
                'addcarts.admin_id',
                'addcarts.twitter_price',
                'addcarts.youtube_price',
                'addcarts.face_price',
                'addcarts.wordpress_price',
                'addcarts.tumblr_price',
                'addcarts.instagram_price',
                'addcarts.quora_price',
                'addcarts.reddit_price',
                'addcarts.koo_price',
                'addcarts.scoopit_price',
                'addcarts.slashdot_price',
                'addcarts.fb_grp_price',
                'addcarts.linkedin_grp_price',
                'addcarts.telegram_price',
                'addcarts.linkedin_price',
                'addcarts.roposo_price',
                'addcarts.chingari_price',
                'addcarts.mitron_price',
                'addcarts.pinterest_price',
                'countries.country_name',
                'states.state_name',
                'cities.city_name'
            )
            ->orderBy('id', 'desc')
            ->get();

            foreach ($data as $row) {
                $sum = 0;
                foreach ($row as $field => $value) {
                    if (strpos($field, '_price') !== false && is_numeric($value)) {
                        // Check if the field name ends with '_price' and the value is numeric
                        $sum += floatval($value);
                    }
                }
                $row->total_price = $sum;
                // Add the total sum to the response data
                log::info("Record ID: {$row->id}, Total Sum of Prices: $sum");
            }

            log::info($data);

            return response()->json($data);
        }
Enter fullscreen mode Exit fullscreen mode

Explanation
Get Current URL and User Email:

$currentURL = url()->current(); 
$login_email = Auth::user()->email;
Enter fullscreen mode Exit fullscreen mode

Database Query

 $data = DB::connection('payments')->table("social_url")
            ->leftJoin('countries', 'social_url.country_id', '=', 'countries.country_id')
            ->leftJoin('states', 'social_url.state_id', '=', 'states.state_id')
            ->leftJoin('addcarts', 'social_url.user_id', '=', 'addcarts.influencer_admin_id')
            ->leftJoin('cities', 'social_url.city_id', '=', 'cities.city_id')
            ->where('social_url.user_email', '<>', $login_email)
            ->select(
                'social_url.*',
                'addcarts.influencer_admin_id',
                'addcarts.cart_socials',
                'addcarts.admin_id',
                'addcarts.twitter_price',
                'addcarts.youtube_price',
                'addcarts.face_price',
                'addcarts.wordpress_price',
                'addcarts.tumblr_price',
                'addcarts.instagram_price',
                'addcarts.quora_price',
                'addcarts.reddit_price',
                'addcarts.koo_price',
                'addcarts.scoopit_price',
                'addcarts.slashdot_price',
                'addcarts.fb_grp_price',
                'addcarts.linkedin_grp_price',
                'addcarts.telegram_price',
                'addcarts.linkedin_price',
                'addcarts.roposo_price',
                'addcarts.chingari_price',
                'addcarts.mitron_price',
                'addcarts.pinterest_price',
                'countries.country_name',
                'states.state_name',
                'cities.city_name'
            )
            ->orderBy('id', 'desc')
            ->get();
Enter fullscreen mode Exit fullscreen mode

this is a complex database query using Laravel's query builder. It fetches data from multiple tables (social_url, countries, states, addcarts, cities) with various joins, conditions, and ordering.
i got data in associative array form output

[
{"id":163,"user_id":"32","user_name":"rakeshkumar","user_email":"vibiv32093@bitofee.com","file_pic":"","slug_id":null,"slug":"rakeshkumar","slugname":null,"country_id":"101","state_id":"4025","city_id":"58078","mobile":"7488127637","digital_marketer":null,"bio":"influencer","social_site":"{\"facebook\":\"https:\\\/\\\/www.facebook.com\\\/ravi.cotocus\",\"twitter\":\"https:\\\/\\\/www.twitter.com\\\/rajesh.cotocus\",\"youtube\":null,\"wordpress\":null,\"tumblr\":null,\"instagram\":null,\"quora\":null,\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","social_price":"{\"facebook\":\"24\",\"twitter\":\"67\",\"youtube\":null,\"wordpress\":null,\"tumblr\":null,\"instagram\":null,\"quora\":null,\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","influencer_admin_id":32,"cart_socials":"{\"facebook\":\"24\"}","admin_id":"26","country_name":"India","state_name":"Jharkhand","city_name":"Bok\u0101ro"},


{"id":157,"user_id":"28","user_name":"admins","user_email":"admins@gmail.com","file_pic":"","slug_id":null,"slug":"admins","slugname":null,"country_id":"101","state_id":"4025","city_id":"58078","mobile":"7488127637","digital_marketer":"faceinfluencer","bio":"dxfbfvb","social_site":"{\"facebook\":\"http:\\\/\\\/www.facebook.com\\\/ravimy.cotocus\",\"twitter\":null,\"youtube\":\"https:\\\/\\\/www.youtube.com\\\/watch?v=4Tpm7D4-6DU&list=RDGMEMPipJmhsMq3GHGrfqf4WIqA&index=27\",\"wordpress\":\"https:\\\/\\\/wordpress.com\\\/\",\"tumblr\":null,\"instagram\":\"https:\\\/\\\/www.instagram.com\\\/\",\"quora\":\"https:\\\/\\\/www.quora.com\\\/\",\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","social_price":"{\"facebook\":\"24\",\"twitter\":null,\"youtube\":\"78\",\"wordpress\":\"88\",\"tumblr\":null,\"instagram\":\"78\",\"quora\":\"55\",\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","influencer_admin_id":28,"cart_socials":"{\"facebook\":\"24\",\"youtube\":\"78\",\"wordpress\":\"88\",\"instagram\":\"78\",\"quora\":\"55\"}","admin_id":"26","country_name":"India","state_name":"Jharkhand","city_name":"Bok\u0101ro"}
]
Enter fullscreen mode Exit fullscreen mode

Calculate Sums and Log Information:

     foreach ($data as $row) {
                $sum = 0;
                foreach ($row as $field => $value) {
                    if (strpos($field, '_price') !== false && is_numeric($value)) {
                        // Check if the field name ends with '_price' and the value is numeric
                        $sum += floatval($value);
                    }
                }
                $row->total_price = $sum;
                // Add the total sum to the response data
                log::info("Record ID: {$row->id}, Total Sum of Prices: $sum");
            }
Enter fullscreen mode Exit fullscreen mode

Add the sum to the response data or existing $data

$row->total_price = $sum;
                // Add the total sum to the response data
                log::info("Record ID: {$row->id}, Total Sum of Prices: $sum");
Enter fullscreen mode Exit fullscreen mode

see differnce check before or after applying query log::info($data);

How to remove nonnumeric char to perform calculation like $70

   foreach ($data as $row) {
                $sum = 0;
                foreach ($row as $field => $value) {
                    if (strpos($field, '_price') !== false && is_numeric($value)) {
                        // Extract numeric part using regular expression
        preg_match_all('/[0-9,.]+/', $value, $matches);
        $numericValue = implode('', $matches[0]);
                        $sum += floatval($numericValue);
                    }
                }
Enter fullscreen mode Exit fullscreen mode

Calculate the sum for each record is in associative array form after json decode 

 public function allinfluencerdata(Request $request)      
         { 
            log::info("data allinfluencerdata");
            $currentURL = url()->current(); 
            $login_email = Auth::user()->email;
            $profiles = Addprofile::all();           
            log::info("data if ke andar hai");  
            $data = DB::connection('payments')->table("social_url")
            ->leftJoin('countries', 'social_url.country_id', '=', 'countries.country_id')
            ->leftJoin('states', 'social_url.state_id', '=', 'states.state_id')
            ->leftJoin('addcarts', 'social_url.user_id', '=', 'addcarts.influencer_admin_id')
            ->leftJoin('cities', 'social_url.city_id', '=', 'cities.city_id')
            ->where('social_url.user_email', '<>', $login_email)
            ->select(
                'social_url.*',
                'addcarts.influencer_admin_id',
                'addcarts.cart_socials',
                'addcarts.admin_id',
                'countries.country_name',
                'states.state_name',
                'cities.city_name'
            )
            ->orderBy('id', 'desc')
            ->get();    
        foreach ($data as $row) {
            $cartSocials = json_decode($row->cart_socials, true);           
            $recordSum = 0;
            foreach ($cartSocials as $platform => $price) {
                $recordSum += $price;
            }    
            // Add the sum to the response data
            $row->record_sum = $recordSum;
            log::info("Record ID: {$row->id}, Total Sum: $recordSum");
        }    
        return response()->json($data);
    }
Enter fullscreen mode Exit fullscreen mode

Explanation
Get Current URL and User Email:

$currentURL = url()->current(); 
$login_email = Auth::user()->email;
Enter fullscreen mode Exit fullscreen mode

Database Query

$data = DB::connection('payments')->table("social_url")
    ->leftJoin('countries', 'social_url.country_id', '=', 'countries.country_id')
    ->leftJoin('states', 'social_url.state_id', '=', 'states.state_id')
    ->leftJoin('addcarts', 'social_url.user_id', '=', 'addcarts.influencer_admin_id')
    ->leftJoin('cities', 'social_url.city_id', '=', 'cities.city_id')
    ->where('social_url.user_email', '<>', $login_email)
    ->select(
        'social_url.*',
        'addcarts.influencer_admin_id',
        'addcarts.cart_socials',
        'addcarts.admin_id',
        'countries.country_name',
        'states.state_name',
        'cities.city_name'
    )
    ->orderBy('id', 'desc')
    ->get();
Enter fullscreen mode Exit fullscreen mode

this is a complex database query using Laravel's query builder. It fetches data from multiple tables (social_url, countries, states, addcarts, cities) with various joins, conditions, and ordering.
i got data in associative array form output

[
{"id":163,"user_id":"32","user_name":"rakeshkumar","user_email":"vibiv32093@bitofee.com","file_pic":"","slug_id":null,"slug":"rakeshkumar","slugname":null,"country_id":"101","state_id":"4025","city_id":"58078","mobile":"7488127637","digital_marketer":null,"bio":"influencer","social_site":"{\"facebook\":\"https:\\\/\\\/www.facebook.com\\\/ravi.cotocus\",\"twitter\":\"https:\\\/\\\/www.twitter.com\\\/rajesh.cotocus\",\"youtube\":null,\"wordpress\":null,\"tumblr\":null,\"instagram\":null,\"quora\":null,\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","social_price":"{\"facebook\":\"24\",\"twitter\":\"67\",\"youtube\":null,\"wordpress\":null,\"tumblr\":null,\"instagram\":null,\"quora\":null,\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","influencer_admin_id":32,"cart_socials":"{\"facebook\":\"24\"}","admin_id":"26","country_name":"India","state_name":"Jharkhand","city_name":"Bok\u0101ro"},


{"id":157,"user_id":"28","user_name":"admins","user_email":"admins@gmail.com","file_pic":"","slug_id":null,"slug":"admins","slugname":null,"country_id":"101","state_id":"4025","city_id":"58078","mobile":"7488127637","digital_marketer":"faceinfluencer","bio":"dxfbfvb","social_site":"{\"facebook\":\"http:\\\/\\\/www.facebook.com\\\/ravimy.cotocus\",\"twitter\":null,\"youtube\":\"https:\\\/\\\/www.youtube.com\\\/watch?v=4Tpm7D4-6DU&list=RDGMEMPipJmhsMq3GHGrfqf4WIqA&index=27\",\"wordpress\":\"https:\\\/\\\/wordpress.com\\\/\",\"tumblr\":null,\"instagram\":\"https:\\\/\\\/www.instagram.com\\\/\",\"quora\":\"https:\\\/\\\/www.quora.com\\\/\",\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","social_price":"{\"facebook\":\"24\",\"twitter\":null,\"youtube\":\"78\",\"wordpress\":\"88\",\"tumblr\":null,\"instagram\":\"78\",\"quora\":\"55\",\"pinterest\":null,\"reddit\":null,\"koo\":null,\"scoopit\":null,\"slashdot\":null,\"telegram\":null,\"fb_grp\":null,\"linkedin_grp\":null,\"linkedin\":null,\"roposo\":null,\"chingari\":null,\"mitron\":null}","influencer_admin_id":28,"cart_socials":"{\"facebook\":\"24\",\"youtube\":\"78\",\"wordpress\":\"88\",\"instagram\":\"78\",\"quora\":\"55\"}","admin_id":"26","country_name":"India","state_name":"Jharkhand","city_name":"Bok\u0101ro"}
]
Enter fullscreen mode Exit fullscreen mode

Calculate Sums and Log Information:

foreach ($data as $row) {
    $cartSocials = json_decode($row->cart_socials, true);
    log::info($cartSocials);

    $recordSum = 0;
    foreach ($cartSocials as $platform => $price) {
        $recordSum += $price;
    }

    // Add the sum to the response data
    $row->record_sum = $recordSum;
    log::info("Record ID: {$row->id}, Total Sum: $recordSum");
}
Enter fullscreen mode Exit fullscreen mode

Record ID: 157, Total Sum: 323
converting json format to associative record for single field

$cartSocials = json_decode($row->cart_socials, true);
Enter fullscreen mode Exit fullscreen mode

before json_decode

{"facebook":"24","youtube":"78","wordpress":"88","instagram":"78","quora":"55"}
Enter fullscreen mode Exit fullscreen mode

after json_decode

 array (
  'facebook' => '24',
  'youtube' => '78',
  'wordpress' => '88',
  'instagram' => '78',
  'quora' => '55',
)
Enter fullscreen mode Exit fullscreen mode

Add the sum to the response data or existing $data

$row->record_sum = $recordSum;
Enter fullscreen mode Exit fullscreen mode

see differnce check before or after applying query log::info($data);
calculate the sum of all social site prices across all records in your dataset

$totalSumAllRecords = 0;

foreach ($data as $row) {
    $cartSocials = json_decode($row->cart_socials, true);
    log::info($cartSocials);

    $recordSum = 0;
    foreach ($cartSocials as $platform => $price) {
        $recordSum += $price;
    }

    // Add the sum to the response data
    $row->record_sum = $recordSum;
    log::info("Record ID: {$row->id}, Total Sum: $recordSum");

    // Accumulate the total sum across all records
    $totalSumAllRecords += $recordSum;
}

log::info("Total Sum Across All Records: $totalSumAllRecords");
Enter fullscreen mode Exit fullscreen mode

Calculate the sum for each social site across all records is in associative array form after json decode 

   public function allinfluencerdata(Request $request)      
         {  

            log::info("data allinfluencerdata");
            $currentURL = url()->current(); 
            $login_email = Auth::user()->email;
            $profiles = Addprofile::all();           
            log::info("data if ke andar hai");  
            $data = DB::connection('payments')->table("social_url")
            ->leftJoin('countries', 'social_url.country_id', '=', 'countries.country_id')
            ->leftJoin('states', 'social_url.state_id', '=', 'states.state_id')
            ->leftJoin('addcarts', 'social_url.user_id', '=', 'addcarts.influencer_admin_id')
            ->leftJoin('cities', 'social_url.city_id', '=', 'cities.city_id')
            ->where('social_url.user_email', '<>', $login_email)
            ->select(
                'social_url.*',
                'addcarts.influencer_admin_id',
                'addcarts.cart_socials',
                'addcarts.admin_id',
                'countries.country_name',
                'states.state_name',
                'cities.city_name'
            )
            ->orderBy('id', 'desc')
            ->get();

       foreach ($data as $row) {
    $cartSocials = json_decode($row->cart_socials, true);

    foreach ($cartSocials as $platform => $price) {
        if (!isset($socialSums[$platform])) {
            $socialSums[$platform] = 0;
        }
        $socialSums[$platform] += $price;
    }
    $row->socialSums = $socialSums;
        log::info("Record ID: {$row->id}, Total Sum: " . json_encode($socialSums));
}

            // Add the sum to the response data

            log::info($data);

            return response()->json($data);
        }
Enter fullscreen mode Exit fullscreen mode

Explanation
Get Current URL and User Email:

$currentURL = url()->current(); 
$login_email = Auth::user()->email;
Enter fullscreen mode Exit fullscreen mode

Calculate Sums and Log Information:

      foreach ($data as $row) {
    $cartSocials = json_decode($row->cart_socials, true);

    foreach ($cartSocials as $platform => $price) {
        if (!isset($socialSums[$platform])) {
            $socialSums[$platform] = 0;
        }
        $socialSums[$platform] += $price;
    }
    $row->socialSums = $socialSums;
        log::info("Record ID: {$row->id}, Total Sum: " . json_encode($socialSums));
}
Enter fullscreen mode Exit fullscreen mode

How to remove nonnumeric char to perform calculation

How to remove nonnumeric char to perform calculation like $70

Calculate the sum for each social site across all records is in associative array form after json decode

{"facebook":"$24","youtube":"78","wordpress":"$88","instagram":"78","quora":"55"}
Enter fullscreen mode Exit fullscreen mode

apply it

$numericPrice = floatval(preg_replace('/[^0-9.]/', '', $price));
Enter fullscreen mode Exit fullscreen mode 
foreach ($data as $row) {
    $cartSocials = json_decode($row->cart_socials, true);

    $socialSums = []; // Initialize $socialSums for each row

    foreach ($cartSocials as $platform => $price) {
        // Remove non-numeric characters and convert to float for summation
        $numericPrice = floatval(preg_replace('/[^0-9.]/', '', $price));

        if (!isset($socialSums[$platform])) {
            $socialSums[$platform] = 0;
        }

        $socialSums[$platform] += $numericPrice;
    }

    $row->socialSums = $socialSums;
    log::info("Record ID: {$row->id}, Total Sum: " . json_encode($socialSums));
}
Enter fullscreen mode Exit fullscreen mode

Calculate the sum for each record is in associative array form after json decode

foreach ($data as $row) {
    $cartSocials = json_decode($row->cart_socials, true);
    log::info($cartSocials);

    $recordSum = 0;
    foreach ($cartSocials as $platform => $price) {
        // Remove non-numeric characters and convert to float for summation
        $numericPrice = floatval(preg_replace('/[^0-9.]/', '', $price));
        $recordSum += $numericPrice;
    }

    // Add the sum to the response data
    $row->record_sum = $recordSum;
    log::info("Record ID: {$row->id}, Total Sum: $recordSum");
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

yogeshwariam profile image

Assignment #3 Day3 Learning Sumary

Yogeshwari -

harishramu profile image

Deman set, Job and CronJob, ConfigMap, Services and Ingress Controller by Harish

Harish G -

prashanthlinga_252 profile image

Kubernetes Day-3 Summary

PrashanthL -

contactsubham_750 profile image

DaemonSet, Job, CronJob,Services , Ingress, Ingress Controller by Subham

Subham Chowdhury -