I have two tables Infos and nationalities like this :

1) Table infos:

1) Table nationalities :

I want to get in the output something like this:

So for that I tried something like this(but it did not help):

$total_nationalite = DB::table('infos')
                    ->select('desig', 'nationalities.id',DB::raw('COUNT(*) as count'))
                    ->join('nationalities', DB::raw('CAST(nationalities.id as varchar)'), '=', 'infos.nationality')
                    ->groupBy('desig','nationalities.id')
                    ->get();

please any suggestion with the query builder or with a simple query in postgres.

CodePudding user response：

Having a comma separated list of values text in nationality field is a bad idea. Have an array of integers or - much better - a normalized design. Anyway here is a native query solution.

select desig, count(*) count  
from
(
  select unnest(string_to_array(nationality, ','))::integer nat_id 
  from infos
) t
join nationalities n on t.nat_id = n.id
group by desig;

DB Fiddle