laravel 4 关系 - 如何显示用户投票记录的前 5 名排名 [英] laravel 4 relations - how display a top-5 ranking of records voted by users

问题描述

我正在创建一个新闻源系统，你很容易猜到，这超出了我的技能.请善待我，让我走上正轨或提供一些我可以继续做的事情.

I am creating a system of newsfeed, and as you can easily guess, it is beyond my skills. Please be kind to put me on the right track or provide something I can go on with.

我有几百个事件(模型名称是 Event1，表 'events')我还有一个数据透视表，用户可以在其中分配任何事件的重要性(值 0、1、2、3)

I have several hundred events (model name is Event1, table 'events') I also have a pivot table in which users can assign any event's importance (values 0,1,2,3)

数据透视表user_attitudes(Model Userattitude)的相关列是id、item_type、item_id、重要性、态度、creator_id一个例子三记录是:

The relevant columns of the pivot table user_attitudes (Model Userattitude) are id, item_type, item_id, importance, attitude, creator_id An example three record are:

456 - 事件 - 678 - 2 - 4

456 - event - 678 - 2 - 4

457 - 事件 - 690 - 3 - 15

457 - event - 690 - 3 - 15

458 - 事件 - 690 - 1 - 4

458 - event - 690 - 1 - 4

459 - 参与者 - 45 - 1 - 4

459 - participant - 45 - 1 - 4

简单英语:事件 #690 的总聚合重要性为4"，而事件 #678 的总聚合重要性为2".因此，在我的排名中，#690 事件应该排在第一位.

Plain English: Total aggregated importance of the event #690 is '4', while the event #678 is '2'. Therefore in my ranking the event #690 should be listed as first.

只是为了看看更大的图片:用户 #4 也将参与者 #45 评为重要性 = 1.

Just to see the bigger pic: the user #4 also rated participant # 45 as importance = 1.

该表为许多模型提供服务 - 上面的例子包括两个 - 只是为了更好地展示我拥有的东西.

The table services many models - the above example include two - just to give a better image of what I have.

1. 我希望打印前 5 项赛事(以及之后的其他模型)的排名.我希望能够使用两种计算总分的方法:

1. I wish to print a ranking of top 5 events (and later other models). I wish to be able to use two methods of calculating the total score:

• 通过计算实际值 (0,1,2,3)
• 将任何大于 0 的值计为 1 分.

我想生成按此条件过滤事件的视图:

I want to generate views which filter events by this criteria:

• 至少有一个用户将重要性设置为0"(我需要它将事件标记为不可信)
• 尚未评级的事件
• 与上述相反 - 至少有一位用户评价过的事件
• 按对其赋予任何重要性的用户数量列出的事件

这很容易，但我仍然不知道如何实现.与上述 #2 相同的过滤器，但与特定用户的决定相关:

This is easy, but still I have no idea how to make it happen. The same filters as the above #2, but related to a particular user decisions:

• 列出用户尚未评级的 5 或 10 个事件(随机或最新)也许这样的事情会是一个答案:

$q->where('creator_id', '=', Auth::user()->id);

$q->where('creator_id', '=', Auth::user()->id);

相关代码:由于我并没有真正掌握合并的关系，我可能无法展示提供帮助所需的一切 - 在评论中要求更多代码.

Relevant code: As I don't really grasp the merged relations, I might fail to show everything needed to provide help - ask for more code in comments.

型号:

事件 1(表事件"):

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();
}

用户:(表 'users' - 标准用户表)

public function importances()
{
    return $this->hasMany('Userattitude', 'creator_id');
}

在模型Userattitude中(不同于User，表名'user_attitudes')

In model Userattitude (different from User, table name 'user_attitudes')

public function events()
{
    return $this->morphTo('item')->where('item_type', 'event');
}



public function event()

    {
        return $this->belongsTo ('Event1', 'item_id');
    }

回复@lucas 的问题:

PROBLEMS IN REPLY TO @lucas answer:

表名items"让我感到困惑，因为在我的项目中，items"是事件(模型 Event1)、参与者(模型实体)和其他对象.在我掌握你提供的知识之前，我们可以坚持我的名字吗?它还包含名为 attitudes 的列，用于将特定项目列入黑名单.例如，实体"类型的项目(多个事件的可能参与者)可以由用户进行两轮投票:- 由用户设置的重要性(我们现在正在这样做，可用的值为 0,1,2,3)- 根据用户对(可能的值 (-1, 0, 1))的态度这样的解决方案允许我计算每个项目的业力.例如-1 x 3 = -3(可能的最坏业力值)，而1 x 2 = 2(中等正业力).

table name 'items' keeps me confused as in my project 'items' are events (model Event1), the participants (model Entity) and other objects. Can we stick to my naming until I get hold of the knowledge you are providing? it also contains column named attitudes, which is used for blacklisting particular items. For instance, an item of type 'entity' (possible participant of multiple events) can be voted by user two-wise: - by importance set by an user (we are doing this now, values available to use are 0,1,2,3) - by attitude of an user toward (possible value (-1, 0, 1) Such solution allows me to compute karma of each item. For instance -1 x 3 = -3 (worst possible karma value), while 1 x 2 = 2 (medium positive karma).

因此，我无法通过 users 方法使用查询.对我来说还是太混乱了，抱歉.我们偏离了我最初的心理形象.考虑这个查询:

In consequence I am unable to use queries with the users method. It is still too confusing to me, sorry. We diverted too far from my original mental image. Consider this query:

$events = Event1::has('users', '<', 1)->get();

如果在 Event1 中我声明

If in Event1 I declare

    public function users()
{
    return $this->morphToMany('User', 'item', null, null, 'creator_id');
}

注意:User 是标准的users 表，其中存储了用户名、密码和电子邮件

Note: User is the standard users table, where username, password and email are stored

我收到此错误:

[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');
}

注意:Userattitude 是我存储用户判断的模型(表名是'user_attitudes').此表包含重要性"和态度"列.

Note: Userattitude is model (table name is 'user_attitudes') where i store user judgments. This table contains columns 'importance' and 'attitude'.

我遇到了同样的错误.

如果我将方法更改为

    public function users()
{
    return $this->morphToMany('User', 'Userattitudes', null, null, 'creator_id');
}

我明白了:

[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 usersinner join user_attitudes on users.id = user_attitudes.creator_id 其中 user_attitudes.Userattitudes_id = Events.id 和 user_attitudes.Userattitudes_type = Event1) >= 1) [] []

[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 on users.id = user_attitudes.creator_id where user_attitudes.Userattitudes_id = Events.id and user_attitudes.Userattitudes_type = Event1) >= 1) [] []

可能的解决方案:名为items"的user_attitudes"表别名.我可以创建一个具有所需名称的视图.我做到了，但现在查询没有结果.

Possible solution: the 'user_attitudes' table alias with name 'items'. I could create a view with the required name. I did it, but now the query produces no results.

我应该将creator_id 重命名为user_id - 还是保留两列并在其中保留重复的信息?creator_id 遵循约定，我用它来创建记录......如何解决这个难题?

should I rename creator_id into user_id - or keep both columns and keep duplicated information in them? The creator_id follows conventions and I use it to create records... how to resolve this dillema?

据我所知，如果我想获得与用户相关的前 5 个事件的列表，我需要在代码中添加另一行，将搜索范围缩小到特定登录用户创建的记录:

As far as I understand, if I want to get a USER-RELATED list of top-5 events, I need to ad another line to the code, which narrows search scope to records created by a particular logged in user:

Auth::user()->id)

代码如下所示:重要性为 0 的所有

$events = Event1::whereHas('users', function($q){
$q->where('importance', 0);
$q->where('creator_id', '=', Auth::user()->id);

})->get();

对吗?

好的，我现在可以输出这样的查询:

Ok, I am now able to output a query like these:

$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(); 

在 foreach 循环中，我可以使用以下代码显示总重要性计数:

and in the foreach loop I can display the total importance count with this code:

{{$e->importance or '-'}}

但是我如何显示替代查询的计数:来自另一列的值的总和，命名态度，可以在这个单独的查询中计算:

But How I could display count of an alternative query: SUM of values from another column, named attitude, which can be computed in this SEPARATE query:

换句话说，在我的@foreach 循环中，我需要同时显示 $e->importance 和计算的 SUM(user_attitudes.attitude) AS karma，现在可以通过此查询接收:

In other words, in my @foreach loop I need to display both $e->importance and a computed SUM(user_attitudes.attitude) AS karma, which for now can be received 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(); 

我的解决方案是在实体"表中创建一些额外的列:- karma_negative- karma_positive每次有人投票时存储/更新投票总数.

My solution would be to create some extra columns in the 'entities' table: - karma_negative - karma_positive to store/update total amount of votes each time someone is voting.

推荐答案

首先说一下设置.我不完全确定你的工作方式和是否有效，但我在我的测试实例上创建了它并且它有效，所以我建议你相应地改变你的:

First, let's talk about the setup. I wasn't entirely sure how and if your's works but I created this on my testing instance and it worked, so I recommend you change yours accordingly:

这很简单(你可能已经有了它

That's a simple one (and you probably already have it like this

• id(主键)
• 名字(或类似的名字)
• 等

我不确定在你的例子中是否是 Userattitude 但我不这么认为......

I'm not sure if in your example that is Userattitude but I don't think so...

• id(主键)
• 电子邮件 (?)
• 等

这一点很重要.数据透视表.名称可以不同，但​​为了保持简单并遵循约定，它应该是多态关系的复数(在您的情况下item => items)

This is the important one. The pivot table. The name can be different but to keep it simple and follow conventions it should be the plural of the polymorphic relation (in your case item => items)

• id(实际上甚至没有必要，但我把它留在了那里)
• item_type
• item_id
• 重要性
• creator_id(考虑将其更改为 user_id.这将简化关系声明)

• id (actually not even necessary, but I left it in there)
• item_type
• item_id
• importance
• creator_id (consider changing that to user_id. This would simplify the relationship declaration)

我认为您必须阅读文档再次.你宣布了​​几个奇怪的关系.我是这样做的:

I think you have to read the docs again. You had several weird relations declared. Here's how I did it:

默认情况下，Laravel 使用类名 (get_class($object)) 作为数据库中 ..._type 列的值.要更改它，您需要在模型中定义 $morphClass.

By default Laravel uses the classname (get_class($object)) as value for the ..._type column in the database. To change that 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');
    }
}

用户

class User extends Eloquent implements UserInterface, RemindableInterface {

    // ... default laravel stuff ...

    public function events(){
        return $this->morphedByMany('Event1', 'item', null, null, 'creator_id');
    }
}

好的，现在我们可以开始了.第一个附加信息.我尽可能使用 Eloquent 关系.在所有查询中，join() 使用关系会变慢，因为某些事情(如计数或计算最大值)必须在查询后在 PHP 中完成.MySQL 在这些方面做得很好(在性能方面也很出色).

Alright now we can get started. First one additional information. I used Eloquent relations whenever possible. In all the queries a join() is made it would be slower to use relations because certain things (like counting or calculating the maximum) would have to be done in PHP after the query. And MySQL does a pretty good job (also performance wise) at those things.

$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();

票数超过 0 的前 5 名

$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();

所有都具有重要性 0

$events = Event1::whereHas('users', function($q){
    $q->where('importance', 0);
})->get();

全部没有任何投票

$events = Event1::has('users', '<', 1)->get();

全部获得 1+ 票

$events = Event1::has('users')->get();

全部按票数排序

$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();

没有投票的最新 5 个

如果你使用 Eloquents 时间戳 created_at:

$events = Event1::has('users', '<', 1)->latest()->take(5)->get();

如果不是(按最大 ID 排序):

If you're not (order by greatest id):

$events = Event1::has('users', '<', 1)->latest('id')->take(5)->get();

无票随机5

$events = Event1::has('users', '<', 1)->orderByRaw('RAND()')->take(5)->get();

我没有故意对查询添加任何解释.如果您想了解更多有关特定内容或需要帮助的信息，请发表评论

这篇关于laravel 4 关系 - 如何显示用户投票记录的前 5 名排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！