Convert raw SQL to Laravel Query Builder

Question

I'm having difficulty converting this SQL query to Laravel Query Builder.I tried for hours but couldn't get my head around this.I tried online tools for converting SQL to Query builder but didn't work.

Here is my code:

SELECT 
   technologies.name_en,
   Count(cig_members.id) AS CigTotal,
   Count(CASE
           WHEN cig_members.is_ethnic = 1 THEN 1
           ELSE NULL
         END) AS CigTotalEthnic,
   Count(CASE
           WHEN cig_members.gender = 'female' THEN 1
           ELSE NULL
         END) AS CigTotalFemale,
   Count(CASE
           WHEN cig_members.gender = 'female'
                AND cig_members.is_ethnic = 1 THEN 1
           ELSE NULL
         END) AS CigTotalEthnicFemale,
   Count(farmers.id) AS NonCigTotal,
   Count(CASE
           WHEN farmers.is_ethnic = 1 THEN 1
           ELSE NULL
         END) AS NonCigtoTalEthnic,
   Count(CASE
           WHEN farmers.gender = 'female' THEN 1
           ELSE NULL
         END) AS NonCigTotalFemale,
   Count(CASE
           WHEN farmers.gender = 'female'
                AND farmers.is_ethnic = 1 THEN 1
           ELSE NULL
         END) AS NonCigtTotalEthnicFemale
   FROM   adopting_technologies
   JOIN adopting_farmers
          ON adopting_farmers.id = adopting_technologies.adopting_farmer_id
   LEFT JOIN cig_members
          ON cig_members.id = adopting_farmers.cig_member_id
   LEFT JOIN farmers
          ON farmers.id = adopting_farmers.farmer_id
   LEFT JOIN financial_years
          ON financial_years.id = adopting_farmers.financial_year_id
   LEFT JOIN technologies
          ON technologies.id = adopting_technologies.technology_id
   GROUP  BY adopting_technologies.technology_id

How can I convert this?

Do you already have a model for adopting_technologies ? Or wanna use generic ORM? — Techno
– Techno, Commented Jun 1, 2021 at 9:59

Techno · Accepted Answer · 2021-06-01 10:10:08Z

It should look roughly like this:

$result = AdoptingTechnology::selectRaw("
        technologies.name_en,
   Count(cig_members.id) AS CigTotal,
   Count(CASE
           WHEN cig_members.is_ethnic = 1 THEN 1
           ELSE NULL
         END) AS CigTotalEthnic,
   Count(CASE
           WHEN cig_members.gender = 'female' THEN 1
           ELSE NULL
         END) AS CigTotalFemale,
   Count(CASE
           WHEN cig_members.gender = 'female'
                AND cig_members.is_ethnic = 1 THEN 1
           ELSE NULL
         END) AS CigTotalEthnicFemale,
   Count(farmers.id) AS NonCigTotal,
   Count(CASE
           WHEN farmers.is_ethnic = 1 THEN 1
           ELSE NULL
         END) AS NonCigtoTalEthnic,
   Count(CASE
           WHEN farmers.gender = 'female' THEN 1
           ELSE NULL
         END) AS NonCigTotalFemale,
   Count(CASE
           WHEN farmers.gender = 'female'
                AND farmers.is_ethnic = 1 THEN 1
           ELSE NULL
         END) AS NonCigtTotalEthnicFemale
         ")
            ->join('adopting_farmers', 'adopting_farmers.id','adopting_technologies.adopting_farmer_id')
            ->leftJoin('cig_members','cig_members.id','adopting_farmers.cig_member_id')
            ->leftJoin('farmers','farmers.id','adopting_farmers.farmer_id')
            ->leftJoin('financial_years','financial_years.id','adopting_farmers.financial_year_id')
            ->leftJoin('technologies','technologies.id','adopting_technologies.technology_id')
            ->groupBy('adopting_technologies.technology_id')
            ->get();

If you have any issues let me know:) ofc cannot test it locally

habeebdev · Accepted Answer · 2021-06-01 10:22:13Z

1

DB::table('adopting_technologies')
            ->select('adopting_technologies.technology_id', 'technologies.name_en')
            ->selectRaw("
                COUNT(cig_members.id) AS CigTotal,
                COUNT(CASE WHEN cig_members.is_ethnic = 1 THEN 1 ELSE NULL END) AS CigTotalEthnic,
                COUNT(CASE WHEN cig_members.gender = 'female' THEN 1 ELSE NULL END) AS CigTotalFemale,
                COUNT(CASE WHEN cig_members.gender = 'female' AND cig_members.is_ethnic = 1 THEN 1 ELSE NULL END) AS CigTotalEthnicFemale,
                COUNT(farmers.id) AS NonCigTotal,
                COUNT(CASE WHEN farmers.is_ethnic = 1 THEN 1 ELSE NULL END) AS NonCigtoTalEthnic,
                COUNT(CASE WHEN farmers.gender = 'female' THEN 1 ELSE NULL END) AS NonCigTotalFemale,
                COUNT(CASE WHEN farmers.gender = 'female' AND farmers.is_ethnic = 1 THEN 1 ELSE NULL END) AS NonCigtTotalEthnicFemale
            ")
            ->join('adopting_farmers', 'adopting_farmers.id', '=', 'adopting_technologies.adopting_farmer_id')
            ->leftJoin('cig_members', 'cig_members.id', '=', 'adopting_farmers.cig_member_id')
            ->leftJoin('farmers', 'farmers.id', '=', 'adopting_farmers.farmer_id')
            ->leftJoin('financial_years', 'financial_years.id', '=', 'adopting_farmers.financial_year_id')
            ->leftJoin('technologies', 'technologies.id', '=', 'adopting_technologies.technology_id')
            ->groupBy('adopting_technologies.technology_id')
            ->get();

edited Jun 1, 2021 at 10:22

answered Jun 1, 2021 at 10:05

habeebdev

2782 silver badges6 bronze badges

4 Comments

Techno Over a year ago

You are using the wrong joins so this will corrupt the result

habeebdev Over a year ago

@RobBiermann sorry, overlooked that. Upated answer.

Techno Over a year ago

And the user said he has a model, so need to use ORM :)

shaedrich Over a year ago

@RobBiermann See my answer below for an Eloquent approach.

shaedrich · Accepted Answer · 2021-06-01 10:19:57Z

class adopting_technologies extends Model
{
    public function newCollection(array $models = [])
    {
        return new FarmerCollection($models);
    }
    public function adopting_farmers()
    {
        return $this->belongsTo(adopting_farmers::class);
    }
    // other relations for 'cig_members', 'farmers', 'financial_years' and 'technologies'
}

namespace App\Collections;

use Illuminate\Database\Eloquent\Collection;
 
class FarmerCollection extends Collection
{

    public function cigTotal()
    {
        return $this->pluck('cig_members')->map(function($items) { return $items->count(); })->sum();
    }
    // other totals for cigTotalEthnic, CigTotalFemale, CigTotalEthnicFemale, NonCigTotal, NonCigtoTalEthnic, NonCigTotalFemale, NonCigtTotalEthnicFemale
}

Collectives™ on Stack Overflow

Convert raw SQL to Laravel Query Builder

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related