Laravel雄辩-使用WhereIn函数时获取最新行 [英] Laravel eloquent - get the most recent row when using WhereIn function

问题描述

我目前有一个功能:

Score::whereIn('site_id', $sites)
      ->where('type', 1)
      ->avg('score');

但是，如您所见，目前，它在分数"表中的所有结果中平均.我只想对每个$site_id的最新行取平均值(尽管表中可能没有所有$ site_id的结果).

But at the moment, as you can see, it averages out amongst all results within the Scores table. I want to only average out against the most recent row of each $site_id (the table may not have all $site_id's in the results though).

该表具有created_at列，该列将按日期/时间确定哪一个是最新的.

The table has a created_at column, which will determine which is the latest one by date/time.

推荐答案

我不确定Laravel的活动记录样式查询生成器是否有一种简便的方法.

I'm not sure that there's an easy way to do this with Laravel's active record style query builder.

我一直在做这样的事情，它一直需要一个子查询或一个临时表，因为GROUP BY(特别是在MySQL中)将尊重ORDER BY AFTER ，该分组已完成，这意味着您无法保证预先分组的目的是为分组选择的值是符合您的ORDER BY的值.

All the times I've done something like this it has required a sub-query or a temporary table, because GROUP BY (in MySQL specifically) will honor the ORDER BY AFTER the grouping is complete, which means you can't guarantee pre-group that the value selected for the grouping is the one that conforms to your ORDER BY.

在这种情况下，我认为您实际上可能需要2个子查询:第一个对行进行分组以进行排序，第二个对分组进行排序，以便每个site_id有1行，最后一个进行平均.如果仅执行第一个子查询，则最终将得到每个单独site_id的平均值，而不是所有site_id的平均值.

In this case I think you might actually need 2 sub-queries: the first to order the rows for grouping, the second to do the grouping so there is 1 row per site_id, and the final select to do the averaging. If you do just the first sub-query you will end up with an average for each individual site_id, instead of one average for all site_id's.

以下是我想查找的示例查询:

Here's an example query of what I think you're looking for:

SELECT AVG('score') FROM (
    SELECT * FROM (
        SELECT * FROM table_name ORDER BY created_at DESC
    ) AS tbl GROUP BY site_id
) AS tbl2;

未经测试，但我认为通常是正确的.

That's untested, but I think it's generally correct.

...所有这些都假设我已经正确地解释了这个问题，希望我做到了.

...all this assumes I've interpreted the question correctly, hopefully I did.

我希望我有比2个子查询更好的建议-如果其他人有更好的方法可以做到这一点，我希望学习新的东西.

I wish I had a better suggestion than 2 sub-queries - if anyone else has a better way of doing this, I'd love to learn something new.

现在，使用Laravel的活动记录样式查询生成器来执行此操作是我必须去查找的，以查看是否有可能.可能只需要运行原始查询即可.但是我想我应该先发布方法论，然后再进一步研究.

Now, doing this with Laravel's active record style query builder is something I'd have to go look up to see if it's even possible. Might need to just go run the raw query. But I figured I'd post the methodology to see if it's sound before digging in any further.

这篇关于Laravel雄辩-使用WhereIn函数时获取最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！