fetch other(custom) column with group by and count using laravel

22
January 12, 2019, at 1:50 PM

I want to fetch vacancies from the vacancies table.

vacancies table

id | position

1 | Laravel dev

2 | Php dev

3 | Laravel dev

4 | Laravel dev

5 | Laravel dev

I want to fetch vacancies using some where statement but result should be with count.

As you can see in above screenshot... I am getting only Position column and Count for this i am using following query-

$vacancies = Vacancy::where('tag_category_id', $TagCatId)
            ->select('position', DB::raw('count(*) as total'))
            ->groupBy('position')
            ->get();

What if i want ID column as well in this result?

Answer 1

you need to first set strict key as false in database.php in mysql connection

 'strict' => false,   //change true to false ..

then run this command clear config

php artisan config:cache

then run this query

Vacancy::where('tag_category_id', $TagCatId)
            ->select('id','position', DB::raw('count(*) as total'))
            ->groupBy('position')
            ->get();

read more about strict mode see

if you want to customize in mode you can do that see

Disabling it wouldn't make your website unsecured if you handle all validations in your controllers and follow best practices like Laravel already does out of the box.

Answer 2

Try using the GROUP_CONCAT:

$vacancies = Vacancy::where('tag_category_id', $TagCatId)
            ->select(DB::raw('GROUP_CONCAT(id) AS IDs','position', DB::raw('count(*) as total'))
            ->groupBy('position')
            ->get();

This will give you a comma separated list of IDs for each position

I ran this on my users table so ignore the column names but you will get a result in this format:

My query:

Users::select(DB::raw('GROUP_CONCAT(id) AS IDs'), DB::raw('count(*) as Total'), 'team_id')
->groupBy('team_id')
->get();

My output:

App\Users {#3297
         IDs: "9,69,70,72,73",
         Total: 5,
         team_id: 11,
       },
READ ALSO
Returning data in html(firebase & php - Firebase Admin SDK for PHP)

Returning data in html(firebase & php - Firebase Admin SDK for PHP)

I'm confused about the routing system in the Firebase Admin SDK for PHP, I want to display html pages, and have and direct users to different pages like like home, about, account, profile and ect but I don't know how to

44
How to calculate DF in TF-IDF with PHP

How to calculate DF in TF-IDF with PHP

I have a pseudocode about TF IDF, and i'm still confused how to calculate document frequency

42
Laravel working with collection filter and map

Laravel working with collection filter and map

I have a method where I can return the upcoming payment dateIn the below example, getNextDate() will return a Carbon date, like so:

54