0

I'm having issues getting a proper count total with my Laravel model.

Model Structure

  • User
  • Item
  • ItemLike

A user can have multiple Items, and each of these Items can have multiple ItemLikes (when a user 'likes' the item).

I can easily get the individual ItemLike counts when using an Item model:

return $this->itemLikes()->count();

But I can't figure out how to get the total # of ItemLike's a User has across all the Item's he owns.

EXAMPLE

User A has 3 Items. Each Item has 5 ItemLike's, for a grand total of 15.

I tried using eager loading on the User model like this:

return $this->items()->with('itemlikes')->get()->count();

But that returns 3 (the # of Items)

These are the queries it ran, which appears like the second query is the one I want, yet every way I try it I still get 3 instead of 15

select * from `items` where `items`.`user_id` = '1000'
select * from `item_likes` where `item_likes`.`item_id` in ('1000', '1001', '1002')
Nick Carson
  • 678
  • 1
  • 4
  • 11
  • You need to join the tables... Have you researched how to do this yet? – John Ruddell Aug 25 '14 at 01:37
  • possible duplicate of [Laravel join with 3 Tables](http://stackoverflow.com/questions/18388664/laravel-join-with-3-tables) – John Ruddell Aug 25 '14 at 01:40
  • @JohnRuddell no I have not. Any specific article I should look at? – Nick Carson Aug 25 '14 at 01:51
  • If you look at that link in the comment there's an example of joining the tables.. If you join them you should be able to get the correct count – John Ruddell Aug 25 '14 at 02:00
  • Is there not a way to do this more simply with the eager loading & models? If the second query it runs was select count(*) it would give me the proper result, without joining tables. – Nick Carson Aug 25 '14 at 02:05

3 Answers3

1

After suggestions from others I found 2 solutions to get the result.

Using whereIn:

$itemViewCount = ItemView::
whereIn('item_views.item_id', $this->items()->lists('id'))
->count();

return $itemViewCount;

2 queries for a total of 410μs

Using join:

$itemViewCount = $this->items()
->join('item_views', 'item_views.item_id', '=', 'items.id')
->count();

return $itemViewCount;

2 queries for a total of 600μs

Nick Carson
  • 678
  • 1
  • 4
  • 11
0

Isn't it just a case of creating a method that would return the number of items for the model. e.g.:

#UserModel
public function nbLikes()
{
    $nbLikes = 0;
    foreach($this->items() as $item) {
        $nbLikes += $item->itemLikes()->count();
    }

    return $nbLikes;
}

And then User::nbLikes() should return the piece of data you are looking for?

ornous
  • 449
  • 2
  • 9
  • 1
    This works, but according to other posts I've read leads to the n+1 problem of running a query for every single item instead of 1 query counting them all. If a User had 10,000 Items, it would have to run 10,001 queries to get the count. – Nick Carson Aug 25 '14 at 01:48
-1

try this: $query="select count(il.id) from item_likes il,item itm where il.item_id=itm.id and tm.user_id=1000";

themaheshe
  • 1
  • 2