[FIXED] Complex join with different data types in laravel 8 query builder

Issue

I have two tables Infos and nationalities like this :

1) Table infos:

id(pk)	name	nationality(varchar) accept multiple nationalities
1	John	1,2,3
2	Camilia	2,4

1) Table nationalities :

id(pk) int	desig
1	German
2	Turkey
3	Algeria
4	Qatar

I want to get in the output something like this:

desig	count
German	1
Turkey	2
Algeria	1
Qatar	1

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.

Solution

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

desig	count
German	1
Qatar	1
Algeria	1
Turkey	2

Answered By - Stefanov.sm