Laravel 4 relationships - how to display the rating of top 5 posts voted by users
I am building a news system and as you can easily guess this is beyond my skill set. Please be so kind to put me on the right track or provide something I can continue with.
I have several hundred events (the model name is Event1, table 'events') I also have a pivot table where users can assign any event value (values 0,1,2,3)
Appropriate columns PivotTable user_attitudes (Model Userattitude) are
id
, item_type
, item_id
, importance
, attitude
, creator_id
EXAMPLE three records:
456 - event - 678 - 2 - 4
457 - event - 690 - 3 - 15
458 - event - 690 - 1 - 4
459 - participant - 45 - 1 - 4
Plain English: the total aggregated significance of event # 690 is "4" and event # 678 is "2". Therefore, in my rating, event # 690 should be listed as the first.
Just to see the big picture: User # 4 also rated # 45 as Importance = 1.
The table caters for many models - the example above includes two - just for the best depiction of what I have.
WHAT I NEED:
-
I want to print a ranking of the top 5 events (and later than other models). I want to be able to use two methods to calculate the overall score:
- by calculating the actual value (0,1,2,3)
- counting any value above 0 as 1 point.
-
I want to generate views that filter events by these criteria:
- at least one user has set the value to "0" (I need this event to be invalid)
- events that have not yet been evaluated
- the downside of the above events that are rated by at least one user.
- events listed by the number of users who give it any meaning
-
It's easy, but I don't know how to do it. Same filters as above # 2, but related to specific user decisions :
- a list of 5 or 10 events (random or newest) that have not yet been rated by the user, maybe something like this would be the answer:
$ q-> where ('creator_id', '=', Auth :: user () → id);
Relevant code: Since I don't really understand the pooled relationship, I may not show everything needed to provide help - ask for more comments in the comments.
Models:
Event1 (table events):
public function importances()
{
return $this->morphMany('Userattitude', 'item');
}
public function user_importance($user)
{
return $this->morphMany('Userattitude', 'item')->where('creator_id', ($user ? $user->id : NULL))->first();
}
User: ("users" table - standard user table)
public function importances()
{
return $this->hasMany('Userattitude', 'creator_id');
}
In the Userattitude model (other than User, the table name is 'user_attitudes')
public function events()
{
return $this->morphTo('item')->where('item_type', 'event');
}
public function event()
{
return $this->belongsTo ('Event1', 'item_id');
}
PROBLEMS ANSWERS AT @lucas answer:
PROBLEM 1.
the table name "elements" confuses me, because my project "elements" have events (Event1 model), participants (Entity model) and other objects. Can we stick to my name until I get the knowledge you provide? it also contains a column named relationship that is used to blacklist specific items. For example, an element of type "entity" (a possible participant in several events) can be voted by the user for two reasons: - the importance set by the user (we are doing this now, the available values for use are 0,1,2,3) - the relation user k (possible value (-1, 0, 1) This solution allows me to calculate the karma of each item. For example, -1 x 3 = -3 (the worst possible karma value), and 1 x 2 = 2 (average positive karma).
As a result, I am unable to use requests with the users method . It's too difficult for me anyway, sorry. We've pushed too far away from my original mental image. Consider this request:
$events = Event1::has('users', '<', 1)->get();
If in Event1 I declare
public function users()
{
return $this->morphToMany('User', 'item', null, null, 'creator_id');
}
Note. User is a standard user table where username, password, and email address are stored.
I am getting this error:
[2014-12-28 05:02:48] production.ERROR: FATAL DATABASE ERROR: 500 = SQLSTATE[42S02]: Base table or view not found: 1146 Table 'niepoz_niepozwalam.items' doesn't exist (SQL: select * from `Events` where (select count(*) from `users` inner join `items` on `users`.`id` = `items`.`creator_id` where `items`.`item_id` = `Events`.`id` and `items`.`item_type` = Event1) >= 1) [] []
if i change the method definition to
public function users()
{
return $this->morphToMany('Userattitude', 'item', null, null, 'creator_id');
}
Note. Userattitude is a model (the table name is "user_attitudes") where I store user judgments. This table contains the column meaning and relationship.
I am getting the same error.
If you change the method to
public function users()
{
return $this->morphToMany('User', 'Userattitudes', null, null, 'creator_id');
}
I get this:
[2014-12-28 05:08:28] production.ERROR: FATAL DATABASE ERROR: 500 = SQLSTATE [42S22]: Column not found: 1054 Unknown column 'user_attitudes.Userattitudes_id' in 'where clause' (SQL: select * from Events
where (select count (*) from users
inner join user_attitudes
to users
. id
= user_attitudes
. creator_id
where user_attitudes
. Userattitudes_id
= Events
. id
and user_attitudes
. Userattitudes_type
= Event1)> = 1) [] []
Possible solution: alias the user_attitudes table named "items". I could create a view with the required name. I did it, but now the query is giving no results.
PROBLEM 2
should I rename creator_id to user_id - or keep both columns and keep duplicate information in them? Creator_id follows conventions and I use it to create entries ... how to resolve this dillema?
PROBLEM 3.
As I understand it, if I want to get a list of the 5 best USER related events, I need to add another line of code that narrows the search to records generated by a specific registered user:
Auth::user()->id)
The code will look like this: Everything with importance 0
$events = Event1::whereHas('users', function($q){
$q->where('importance', 0);
$q->where('creator_id', '=', Auth::user()->id);
}) → we get ();
right?
PROBLEM 5:
Ok, now I can output a query like this:
$rank_entities = Entity::leftJoin('user_attitudes', function($q){
$q->on('entity_id', '=', 'entities.id');
$q->where('item_type', '=', 'entity');
})
->selectRaw('entities.*, SUM(user_attitudes.importance) AS importance')
->groupBy('entities.id')
->orderBy('importance', 'desc')
->take(6)
->get();
and in the foreach loop, I can display the total number of values with this code:
{{$e->importance or '-'}}
But how can I display the counter of the alternative query: SUM of values from another column, named relation, which can be calculated in this SEPARATE query:
In other words, in my @foreach loop, I need to display both the $ e-> value and the calculated SUM (user_attitudes.attitude) AS karma , which can now be retrieved with this query
$rank_entities = Entity::leftJoin('userattitudes', function($q){
$q->on('entity_id', '=', 'entities.id');
$q->where('item_type', '=', 'entity');
})
->selectRaw('entities.*, SUM(userattitudes.karma) AS karma')
->groupBy('entities.id')
->orderBy('karma', 'desc')
->take(5)
->get();
My solution would be to create additional columns in the "entities" table: - karma_negative - karma_positive store / update the total number of votes every time someone votes.
source to share
Let's talk about the setup first. I wasn't quite sure how and if yours works, but I created this on my test instance and it worked, so I recommend that you change your variation accordingly:
Database
events
It's simple (and you probably already have it like
- id (primary key)
- name (or something like that)
- etc.
users
I'm not sure what it is in your example Userattitude
, but I don't think so ...
- id (primary key)
- email (?)
- etc.
the elements
It is important. Pivot table. The name may be different, but to keep it simple and follow conventions, it must be the plural of the polymorphic relationship (in your case item => items
)
- id (actually not even needed, but I left it there)
- item_type
- item_id
- value
- creator_id (consider changing this to
user_id
. This will make it easier to declare relationships)
Models
I think you need to read the docs again . You have had some strange relationships. This is how I did it:
Event1
By default, Laravel uses the class name ( get_class($object)
) as the value for a column ..._type
in the database. To change this, you need to define $morphClass
in your models.
class Event1 extends Eloquent {
protected $table = 'events';
protected $morphClass = 'event';
public function users()
{
return $this->morphToMany('User', 'item', null, null, 'creator_id');
}
}
User
class User extends Eloquent implements UserInterface, RemindableInterface {
// ... default laravel stuff ...
public function events(){
return $this->morphedByMany('Event1', 'item', null, null, 'creator_id');
}
}
Inquiries
Ok, now we can start. Add some additional information first. I used the Bad relationship whenever possible. It join()
is slower to use relationships in all queries because some things (like counting or calculating the maximum) have to be done in PHP after the request. And MySQL does a pretty good job (also performance wise) in these things.
Top 5 by overall value
$events = Event1::leftJoin('items', function($q){
$q->on('item_id', '=', 'events.id');
$q->where('item_type', '=', 'event');
})
->selectRaw('events.*, SUM(items.importance) AS importance')
->groupBy('events.id')
->orderBy('importance', 'desc')
->take(5)
->get();
Top 5 by number of votes for 0
$events = Event1::leftJoin('items', function($q){
$q->on('item_id', '=', 'events.id');
$q->where('item_type', '=', 'event');
$q->where('importance', '>', 0);
})
->selectRaw('events.*, COUNT(items.id) AS importance')
->groupBy('events.id')
->orderBy('importance', 'desc')
->take(5)
->get();
Everything with importance 0
$events = Event1::whereHas('users', function($q){
$q->where('importance', 0);
})->get();
All without a vote
$events = Event1::has('users', '<', 1)->get();
All with 1+ votes
$events = Event1::has('users')->get();
Everything is ordered by the number of votes
$events = Event1::leftJoin('items', function($q){
$q->on('item_id', '=', 'events.id');
$q->where('item_type', '=', 'event');
})
->selectRaw('events.*, COUNT(items.id) AS count')
->groupBy('events.id')
->orderBy('count', 'desc')
->get();
New 5 no votes
If you are using YELLOW timestamps created_at
:
$events = Event1::has('users', '<', 1)->latest()->take(5)->get();
If you are not (order by largest id):
$events = Event1::has('users', '<', 1)->latest('id')->take(5)->get();
Random 5 no votes
$events = Event1::has('users', '<', 1)->orderByRaw('RAND()')->take(5)->get();
I have not added any explanations to the target queries. If you want to know more about something specific or need help, write a comment
source to share
PROBLEM 4: SOLVED! (credit to @lukasgeiter)
If you want to display the ranking of items and restrict the results to a specific tag defined in the pivot table, this is the solution:
$ events = Event1 (table name = "events")
For example, the tag will be war : defined in the eventtags table
The nature of the event is defined as
id = '1' is name = 'wars' id = '2' - name = 'conflict' pivot table that assigns multiple tags : event_eventtags , they are defined as id = '4'
Examples of entries for event_eventtags :
id - event_id - eventtag_id
1 - 45 - 1
2 - 45 - 2
Plain English: Event1 # 45 tagged war (# 1) and conflict (# 2)
Now, to print a list of 10 wars, you must define your query like this:
$events= Entity::join('event_eventtags', function($q){
$q->on('entity_id', '=', 'entities.id');
$q->where('entitycapacitytypes_id', '=', 1);
})->leftJoin('user_attitudes', function($q){
$q->on('item_id', '=', 'entities.id');
$q->where('item_type', '=', 'entity');
})
->selectRaw('entities.*, SUM(user_attitudes.importance) AS importance')
->groupBy('entities.id')
->orderBy('importance', 'desc')
->take(10)
->get();
User_attitudes is part of the voting system described in the original question. You can remove it and sort the events in another way.
source to share