Yii2 gridview 显示比表更多的记录 [英] Yii2 gridview showing more records than the table
问题描述
我有两个 mysql 表:
I have two mysql tables:
人有 51 条记录
- id
- 姓名
- ...
人员统计有 131 条记录
- id
- 人名
- 日期
- 星星
- ...
如果我在models/peopleSearch.php 设置这个
if i set this at models/peopleSearch.php
$query->joinWith('peopleStats');
GridView 显示 131 条记录.我如何才能将该连接设置为仅获取 GridView 的每个人项上最后一天的记录(使用 MAX(date) 进行查询)以仅显示具有最新数据的 51 条记录?
GridView shows 131 records. How could I set that join to just get records for the last day (a query with MAX(date) ) on every people item for GridView to show just the 51 records with the latest data?
谢谢
推荐答案
这是 ActiveDataProvider 支持的 GridView 的常见问题.网格需要知道您的数据集中共有多少个模型.同时，获取和实例化所有模型只是为了计算它们的效率会非常低.这就是 ActiveDataProvider 从 SQL 获取模型总数的原因，假设结果集中的每一行都对应一个模型.
It's a common problem for ActiveDataProvider-backed GridView. The grid needs to know how many total models you have in your dataset. At the same time, fetching and instantiating all models just to count them would be extremely inefficient. That's why ActiveDataProvider gets total model count from SQL, assuming that every row in result set corresponds to a model.
现在，您已经在查询中引入了 JOIN.这会增加结果集中的行数(在您的情况下从 51 增加到 131).模型总数不是唯一中断的东西，你也应该有分页问题.请注意，您可能会使用 all() 函数来获取所有模型(如 People::find()->joinWith('peopleStats')->all() 足够聪明，可以删除多余的行，因此您可以使用 all() 获得 51 个模型.
Now, you've introduced a JOIN to your query. This increases the amount of rows in result set (from 51 to 131 in your case). Total model count is not the only thing that breaks, you should also have a problem with pagination. Note that the all() function that you might use to get all models (as in People::find()->joinWith('peopleStats')->all() is smart enough to remove redundant rows, so you'd get exactly 51 models with all().
现在，有几种解决方法.首先，在数据提供者中执行 joinWith() 的唯一原因是当您需要对相关数据进行过滤或排序时.如果不是这种情况，请使用 with()，你会没事的.
Now, there are several workarounds. First of all, the only reason to do a joinWith() in a data provider is when you need to filter or sort on related data. If this is not the case, use with(), and you'll be fine.
如果您确实需要对相关数据进行过滤/排序，还有另一种解决方法，即仅从主表 ->select('people.*') 中选择列.您仍然可以在您的网格视图中引用相关数据，它只会被延迟加载.
If you do need to filter/sort on related data, there is another workaround, select only columns from your main table ->select('people.*'). You can still reference related data in your grid view, it will just be lazy-loaded.
如果您的结果集很小，特别是如果您不打算使用分页，请考虑切换到 ArrayDataProvider.
If your result set is small, and especially if you're not planning to use pagination, consider switching to ArrayDataProvider.
在您的具体情况下，我建议您创建另一个 hasOne 关系，例如 peopleStatsLast，并在其中实现仅选择给定的最后一组统计数据的查询人，然后懒加载它.查看 MySQL 参考了解更多信息信息.
In your specific case, I suggest you just make another hasOne relation like peopleStatsLast, and in it implement the query that selects only the last set of stats for a given person, then lazy-load it. Check MySQL reference for more information.
这篇关于Yii2 gridview 显示比表更多的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
