Question 17926 in Laravel Question posted in
The official Laravel Documentation can be found here.

create array of record counts with query – Laravel

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    the answer is going to be like this in laravel

    DB::table('visitors')
        ->select('visitors.sex', 'event_visitor.event_id', DB::raw('count(*) as num_visits'))
        ->join('event_visitor','visitors.id','=','event_visitor.visitor_id')
        ->whereIn('event_visitor.event_id',[1,2,3,4,5])
        ->groupBy('visitors.sex','event_visitor.event_id')
        ->get();

  2. 0

    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).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search