php - CakePHP 匹配关联数据,并非所有值都返回
问题描述
我有事件和距离,模型看起来像这样:”
/* EventsTable.php */
class EventsTable extends Table
{
public function initialize(array $config): void
{
...
$this->belongsToMany('Distances', [
'foreignKey' => 'event_id',
'targetForeignKey' => 'distance_id',
'joinTable' => 'distances_events',
'dependent' => true
]);
/*DistancesTable.php*/
class DistancesTable extends Table
{
public function initialize(array $config): void
{
...
$this->belongsToMany('Events', [
'foreignKey' => 'distance_id',
'targetForeignKey' => 'event_id',
'joinTable' => 'distances_events'
]);
}
伴随以下事件(和距离)
- 赛事一:25公里、500公里
- 赛事二:25公里
- 赛事3:100公里
我正在尝试选择在给定的最小和最大距离之间至少有一个距离的所有事件。所以我使用这段代码:
$mindistance = 50;
$maxdistance = 550;
$events = $this->Events
->find()
->contain(['Distances'])
->matching('Distances', function ($q) use ($maxdistance, $mindistance) {
return $q->where(['Distances.title <=' => $maxdistance, 'Distances.title >=' => $mindistance]);
});
哪个返回我:
- 赛事一:500公里
- 赛事3:100公里
我错过了包含数据中的 25 公里距离。如何应用此过滤器,但要保持事件的所有距离?
生成的 SQL:
SELECT
Events.id AS Events__id,
Events.user_id AS Events__user_id,
Events.title AS Events__title,
Events.slug AS Events__slug,
Events.type AS Events__type,
Events.description AS Events__description,
Events.datetime AS Events__datetime,
Events.country AS Events__country,
Events.province AS Events__province,
Events.city AS Events__city,
Events.street AS Events__street,
Events.housenumber AS Events__housenumber,
Events.zipcode AS Events__zipcode,
Events.lat AS Events__lat,
Events.lon AS Events__lon,
Events.price AS Events__price,
Events.active AS Events__active,
Events.created AS Events__created,
Events.modified AS Events__modified,
DistancesEvents.distance_id AS DistancesEvents__distance_id,
DistancesEvents.event_id AS DistancesEvents__event_id,
Distances.id AS Distances__id,
Distances.title AS Distances__title,
Distances.created AS Distances__created,
Distances.modified AS Distances__modified
FROM
events Events
INNER JOIN distances_events DistancesEvents ON Events.id = (DistancesEvents.event_id)
INNER JOIN distances Distances ON (
Distances.title <= '550'
AND Distances.title >= '50'
AND Distances.id = (DistancesEvents.distance_id)
)
SELECT
DistancesEvents.distance_id AS Distances_CJoin__distance_id,
DistancesEvents.event_id AS Distances_CJoin__event_id,
Distances.id AS Distances__id,
Distances.title AS Distances__title,
Distances.created AS Distances__created,
Distances.modified AS Distances__modified
FROM
distances Distances
INNER JOIN distances_events DistancesEvents ON Distances.id = (DistancesEvents.distance_id)
WHERE
DistancesEvents.event_id in (8, 10)
解决方案
在 EventsTable 中创建两个与距离的关系,第一个用于匹配,第二个用于所有距离:
/* EventsTable.php */
class EventsTable extends Table
{
public function initialize(array $config): void
{
...
$this->belongsToMany('MatchedDistances', [
'foreignKey' => 'event_id',
'targetForeignKey' => 'distance_id',
'joinTable' => 'distances_events',
'dependent' => true
]);
$this->belongsToMany('Distances', [
'foreignKey' => 'event_id',
'targetForeignKey' => 'distance_id',
'joinTable' => 'distances_events',
'dependent' => true
]);
然后:
$mindistance = 50;
$maxdistance = 550;
$events = $this->Events
->find()
->contain(['MatchedDistances'])
->contain(['Distances'])
->matching('MatchedDistances', function ($q) use ($maxdistance, $mindistance) {
return $q->where(['MatchedDistances.title <=' => $maxdistance, 'MatchedDistances.title >=' => $mindistance]);
});
推荐阅读
- python - 如何在keras的Model.fit方法中传递validation_data
- java - 如何从多个具有相同名称的提交按钮中进行选择(HtmlUnit,Java)
- javascript - 如何从mxgraph中的子单元格获取父单元格
- java - 使 RelativeLayout 适合所有屏幕尺寸
- react-native - 我在 chrome 扩展中看不到我在 Redux Devtools 中的操作
- jupyter-notebook - 无法打开“编辑键盘快捷键”
- gcc - 为什么我们不能直接从栈帧移动 1 个字节到寄存器?
- mysql - 无法执行系统查询
- sql - SQL Server 存储过程查询连接问题
- php - 未找到“Swoole\MySQL”类