Level 102
What you mean by most votes? One contestant with most? Or votes in total for the whole office? I would probably use one query to determine the offices and a second to get the data
Nov 29, 2021
12
Level 4
Eloquent HasManyThrough with Count and Limit
I need help putting this into Eloquent. I have a setup similar to the documentation, https://laravel.com/docs/8.x/eloquent-relationships#has-many-through except my situation is Offices has many Contestants has many Votes.
Offices
====
id
name
Contestants
========
id
office_id
name
email
Votes
====
id
contestant_id
Unfortunately, I'm not on my work laptop so I don't have code to share, but I'm trying to get the top 3 people (most votes, ties broken by date entered - see Paul and George in Office A), grouped by the offices. I'd like to get the count of the votes for contestants and only the top 3 (3 with most votes). The output should look something to the following:
| office | contestant | date_entered | number_votes |
------------------------------------------------
| Office A | John | 2021-11-01 08:00 | 25 |
| Office A | Paul | 2021-11-03 07:48 | 22 |
| Office A | George | 2021-11-03 08:00 | 22 |
| Office B | Ringo | 2021-11-02 08:00 | 18 |
| Office B | Robert | 2021-11-03 08:00 | 17 |
| Office B | Jimmy | 2021-11-03 08:00 | 16 |
| Office C | John | 2021-11-03 08:00 | 26 |
| Office C | John | 2021-11-03 08:00 | 25 |
| Office C | John | 2021-11-03 08:00 | 24 |
I can get this pretty close in SQL (I can't limit it to only 3 per group), but I'd rather have the data retrieved via Eloquent. Can someone point me in the right direction to get this result?
Also, if someone can show me how to format the above as a proper table that would be great also! Thank you.
Level 4
@Sinnbeck it’d be contestant with most votes by office. We want the top 3 per Office.
Level 4
@Sinnbeck can you explain this " I would probably use one query to determine the offices and a second to get the data", do you mean query to get the offices and then loop through the offices to get the required data?
Level 102
@chrismartinez99 no just make a query that gets top 3 offices so you can use those in the seconds query (whereIn())
I haven't come up with any good queries for this, so I haven't answered. If I come up with something I'll let you know. But try just getting the top the offices in a query first
Level 4
@Sinnbeck I gotcha, but it wasn't the top 3 offices - it's the top 3 contestants in each office. Top 3 being determined as contestants with most number of votes.
Level 102
@chrismartinez99 oh so if all 3 are from same office, then only that office is returned?
Level 4
@Sinnbeck Yes, but I'd need the top 3 contestants from each office. So the top 3 from Office A, top 3 from Office B, and so on.
Level 102
@chrismartinez99 good. My suggestion is then
- Get 3 offices with the highest contestant
- Get 3 for each office (can be done in 1 or 3 queries
Level 73
Something like this should work
SELECT o.name, s.*
FROM (SELECT c.office_id, c.name contestant,
(SELECT count(*)
FROM votes
WHERE contestant_id = c.id) number_votes
FROM contestants c
WHERE c.office_id = o.id
ORDER BY number_votes
LIMIT 3) s,
offices o
ORDER BY o.name
Level 4
I think I got it. @tray2 - That was really close - but it gave the same 3 contestants for each Office. I came up with the following:
$offices = Office::all()->pluck( 'name', 'id' );
$sql = '(';
$limit = 3;
$round_1_start = Carbon::createFromFormat( 'Y-m-d H:i', config( 'round_1_start' ) );
$round_1_end = Carbon::createFromFormat( 'Y-m-d H:i', config( 'round_1_end' ) );
foreach($offices as $key => $value){
$sql .= "SELECT offices.id, offices.NAME, count(v.id) AS vote_count, v.contestant_id, c.photo, c.size,
c.account_number ,c.created_at AS submitted FROM votes v
INNER JOIN contestants c ON c.id=v.contestant_id
JOIN offices ON offices.id=c.office_id
WHERE c.office_id={$key} AND
c.disqualification_id IS NULL AND
c.deleted_at IS NULL AND
v.created_at BETWEEN '{$round_1_start}' AND '{$round_1_end}'
GROUP BY v.contestant_id ORDER BY vote_count DESC,c.created_at LIMIT {$limit})";
if ( $key !== array_key_last( $offices->toArray() ) ) {
$sql .= ' UNION ALL (';
}
}
$x = DB::select( $sql );
I'd like to convert it to Eloquent, but this will work for now.
Level 73
@chrismartinez99 Yes it would give you the top three contestants from each office. You can just add additional sorting and limiting to get the correct result.
Level 4
I was able to update what I had to Eloquent:
$start_date = '2021-11-23 08:00';
$end_date = '2021-12-01 17:00';
$limit = 3;
$results = Office::select( 'id', 'name' )
->withCount( [
'votes' => function ( $query ) use ( $start_date, $end_date )
{
$query->whereBetween( 'votes.created_at', [ $start_date, $end_date ] );
}
] )
->with( [
'contestants' => function ( $query ) use ( $start_date, $end_date )
{
$query->select( 'contestants.id', 'office_id', 'contestants.created_at', 'photo', 'size', 'account_number' )
->has( 'votes', '>', 0 )
->withCount( [
'votes' => function ( $query ) use ( $start_date, $end_date )
{
$query->whereBetween( 'votes.created_at', [ $start_date, $end_date ] );
}
] )
->whereNull( 'disqualification_id' )
->orderBy( 'votes_count', 'DESC' )
->orderBy( 'contestants.created_at' );
}
] )
->orderBy( 'offices.id', 'ASC' )
->get()
->map( function ( $query ) use ( $limit )
{
$query->setRelation( 'contestants', $query->contestants->take( $limit ) );
return $query;
} )
->toArray();
The office.votes_count is the total overall votes for that office. This give me the following output (I limited it to the first office for brevity).:
[0] => Array
(
[id] => 1
[name] => Office A
[votes_count] => 7
[contestants] => Array
(
[0] => Array
(
[id] => 59
[office_id] => 1
[created_at] => 2021-11-09T22:40:30.000000Z
[photo] => submitted/ad421dfc86f9ceea56ac39461672c858.png
[size] => 1925
[account_number] => 7520180603
[votes_count] => 2
[url] => http://localhost/storage/submitted/ad421dfc86f9ceea56ac39461672c858.png
[size_in_kb] => 1.88 KB
[submitted] => 3 weeks ago
)
[1] => Array
(
[id] => 62
[office_id] => 1
[created_at] => 2021-11-09T22:40:30.000000Z
[photo] => submitted/9f6ac9183b131fd4b1993bc1071d9667.png
[size] => 1884
[account_number] => 7652089902
[votes_count] => 2
[url] => http://localhost/storage/submitted/9f6ac9183b131fd4b1993bc1071d9667.png
[size_in_kb] => 1.84 KB
[submitted] => 3 weeks ago
)
[2] => Array
(
[id] => 90
[office_id] => 1
[created_at] => 2021-11-09T22:40:30.000000Z
[photo] => submitted/71e5f0c322342085464cb0b6caa7a22b.png
[size] => 1910
[account_number] => 9735333972
[votes_count] => 1
[url] => http://localhost/storage/submitted/71e5f0c322342085464cb0b6caa7a22b.png
[size_in_kb] => 1.87 KB
[submitted] => 3 weeks ago
)
)
)
Does anyone see an issue with the Eloquent query? Thank you in advance!
Please or to participate in this conversation.