Hello everyone.
I have users, which has some indexes of progress. Depending on how user works in my system he gets some numbers, like, how much clients user has visited. I wrote a query which returns an array with 2 keys: user name and number of visits. I used raw query, and it's quite complex.
Also I have a page where I display user progress, like,
John: 50% Bob: 35%
Depending on the number of visits.
I thought that I might want to display some other user info. But then I need to change the query. What I actually would REALLY like to have, is to simply write
{{ $user->progress(); }}
in order to get number of visits. Right now I have something like a loop set on $data array, and
{{ $item['progress'] }}
which I got from my complex query, which is ugly.
If I write a method progress() which runs a query, then if I loop through users I'll get a lot of queries which is bad. So I want to somehow implement what's called eager loading - get all users and their progress as well. But eager loading works for relationships and I don't have any relationship here.
I though of adding a MySQL View and make a model for it, but it'll be a model for a mysql view, not a mysql table. And the set the relationships between user and progress. Not sure how flexible it is, but actually the question is more about how to get some report data for users without using raw queries and raw arrays, but stick to user objects instead
use this: http://laravel.com/docs/5.0/eloquent#accessors-and-mutators
or perhaps do it in a repository pattern
Ah thanks for reply!
You know, I use mutators and presenters (from laracasts lesson).
The thing I'm concerned is that if I put a getProgress method in my eloquent class, and then I'll loop through users, I'll get a lot of queries. Getting progress is quite complicated, it requires right now 3-4 joins. So the general question is: if I have some big logic concealed into this small progress() method, how can I deal with it so that I don't have a lot of queries?
Right now I have a user repository with getProgress method which is like this (maybe not optimized at all but...):
public function getVisitProgress($usersId, $since, $till)
{
$params = [
'CRM\EloquentModels\Contact',
'CRM\EloquentModels\Touch',
$since,
$till,
join(',', $usersId)
];
return \DB::select("select users.last_name, ifnull(sum(t1.has_visits), 0) as progress
from users
left join (
select contacts.user_id as user_id, if(count(touches.id) >= 1, 1, 0) as has_visits
from contacts
left join activities on activities.activitable_id = contacts.id
left join touches on touches.id = activities.child_id
where activities.activitable_type = ? and
activities.child_type = ? and
touches.created_at between FROM_UNIXTIME(?) and FROM_UNIXTIME(?)
group by contacts.id
) as t1
on t1.user_id = users.id
where users.id in (?)
group by users.id", $params);
}
Then I got an array of data, like user last name and progress, and display it. But after learning about repositories and other stuff, in L5 all my functions is no more than a couple of lines of code, and now I face this ugly SQL query, so I'm thinking how to simplify things
I thought about to load some data behind the scenes and then somehow give the user model access to it, so kind of eager loading.
Thaanks!
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast