create array of record counts with query-CodePudding

DB::table('visitors')
        ->join('event_visitor', 'visitors.id', '=', 'event_visitor.visitor_id')->where('sex', 0)
        ->where('event_visitor.event_id', 1)
        ->count();

this is the query to get the count of men at the visitor's table with an event id of 1

I want to get the record count of men women and kids in 10 events and formate it like this

$men = [100, 200, 300 ,400,500,600,700,800,900,1000];
$women = [100, 200, 300 ,400,500,600,700,800,900,1000];
$kids = [100, 200, 300 ,400,500,600,700,800,900,1000];

is there a way to do this in the database without getting into the n 1 problem?

0 = men
1 = women
2 = kids

CodePudding user response：

Don't know about laravel, but in MySQL you could write a query like

SELECT visitors.sex, event_visitor.event_id, count(*) as num_visits
  FROM visitors INNER JOIN event_visitor ON visitors.id = event_visitor.visitors_id
 WHERE event_visitor.event_id IN (1, 10, 15, 21, 25, 28, 30, 48, 59, 61)
 GROUP BY visitors.sex, event_visitor.event_id;

The numbers given after the IN keyword are ten sample event ids, for which you want to get a result. Note, that this counts visits not visitors. If you want to count distinct visitors, you can count(DISTINCT visitors.id).