在 CakePHP 中选择所有具有事件-> 时间表-> 开始和结束日期之间的日期的事件

The problem lies in that the Event doesn't have the dates in it's table.

Event hasMany Schedule
Schedule belongsTo Event

Schedule hasMany Date
Date belongsTo Schedule

• Events table:事件的详细信息 - 名称、位置、描述...等
• Schedules table:开始和结束日期，带有重复选项
• Dates table:根据Schedules
中的数据创建的事件的实际日期
• Events table: details of the event - name, location, description...etc
• Schedules table: start and end date with repeat options
• Dates table: the actual dates of the event created from the data in Schedules

所以 - 我实际上需要选择在 X 和 Y 日期之间至少有一个日期条目的任何事件.

So - I actually need to select any Events that have at least one Date entry between the X and Y dates.

我还需要能够显示带有事件数据的日期.

I also need to be able to display the dates with the event data.

编辑(修订):

我已经尝试过这个，但它似乎正在检索事件而不考虑日期，但只有在日期落在范围内时才检索日期信息:

I've tried this, but it appears to be retrieving the events regardless of the Date, but only retrieving the Date info if the date falls within the range:

$this->Event->Behaviors->attach('Containable');
$events = $this->Event->find('all', array(
    'limit'=>5,
    'order'=>'Event.created DESC',
    'contain' => array(
    'Schedule' => array(
        'fields'=>array(),
        'Date' => array(
            'conditions'=>array(
                'start >=' => $start_date,
                'start <=' => $end_date,
                )
            )
        )
    ),
));

*只是为了澄清 - Date.start 和 Date.end 始终是相同的日期 - 它们还包括一个时间(两个日期时间字段) - 因此我要针对两者检查开始".

*Just to clarify - Date.start and Date.end are always the same Date - they just also include a time (both datetime fields) - hence why I'm checking "start" against both.

我试过使用可包含的，我试过 unbind/bindModel..etc - 我一定是做错了什么或偏离了轨道.

I've tried using containable, I've tried unbind/bindModel..etc - I must be doing something wrong or off-track.

要记住的事情 - 一旦我弄清楚如何根据日期获取事件，我还需要添加其他条件，如事件类型等等 - 不确定这是否会影响答案.

Something to keep in mind - once I figure out how to get the Events based on the Date, I also need to add on other conditions like Event Types and more - not sure if this would affect the answer(s) or not.

更新:

这就是我正在使用的似乎有效的东西 - 看起来也很丑 - 有什么想法吗?:

Here's what I'm using that seems to work - also seems very ugly - any thoughts?:

function getEvents($opts = null) {
    //$opts = limit, start(date), end(date), types, subtypes, subsubtypes, cities

    $qOpts['conditions'] = array();

    //dates
    $qOpts['start'] = date('Y-m-d') . ' 00:00:00';
    if(isset($opts['start'])) $qOpts['start'] = $opts['start'];

    $qOpts['end'] = date('Y-m-d') . ' 23:59:59';
    if(isset($opts['end'])) $qOpts['end'] = $opts['end'];

    //limit
    $qOpts['limit'] = 10;
    if(isset($opts['limit'])) $qOpts['limit'] = $opts['limit'];

    //fields
    //$qOpts['fields'] = array('Event.id', 'Event.name', 'Event.slug', 'City.name', 'Date.start');  
    // if(isset($opts['fields'])) $qOpts['fields'] = $opts['fields'];


    //date conditions
    array_push($qOpts['conditions'], array(
        "Date.start >=" => $qOpts['start'],
        "Date.start <=" => $qOpts['end'],
    ));

    //cities conditions
    if(isset($opts['cities'])) {
        if(is_array($opts['cities'])) {
            $cityConditions['OR'] = array();
            foreach($opts['cities'] as $city_id) {
                array_push($cityConditions['OR'], array('OR'=>array('Venue.city_id'=>$city_id, 'Restaurant.city_id'=>$city_id)));
            }
            array_push($qOpts['conditions'], $cityConditions);
        }
    }

    //event types conditions
    //$opts['event_types'] = array('1');
    if(isset($opts['event_types'])) {
        if(is_array($opts['event_types'])) {
            $eventTypeConditions['OR'] = array();
            foreach($opts['event_types'] as $event_type_id) {
                array_push($eventTypeConditions['OR'], array('EventTypesEvents.event_type_id' => $event_type_id));
            }
            array_push($qOpts['conditions'], $eventTypeConditions);
        }
    }

    //event sub types conditions
    if(isset($opts['event_sub_types'])) {
        if(is_array($opts['event_sub_types'])) {
            $eventSubTypeConditions['OR'] = array();
            foreach($opts['event_sub_types'] as $event_sub_type_id) {
                array_push($eventSubTypeConditions['OR'], array('EventSubTypesEvents.event_sub_type_id' => $event_sub_type_id));
            }
            array_push($qOpts['conditions'], $eventSubTypeConditions);
        }
    }

    //event sub sub types conditions
    if(isset($opts['event_sub_sub_types'])) {
        if(is_array($opts['event_sub_sub_types'])) {
            $eventSubSubTypeConditions['OR'] = array();
            foreach($opts['event_sub_sub_types'] as $event_sub_sub_type_id) {
                array_push($eventSubSubTypeConditions['OR'], array('EventSubSubTypesEvents.event_sub_sub_type_id' => $event_sub_sub_type_id));
            }
            array_push($qOpts['conditions'], $eventSubSubTypeConditions);
        }
    }


    $this->recursive = 2;

    $data = $this->find('all', array(
        'contain' => array(
            'Restaurant' => array(
                'fields' => array('id', 'name', 'slug', 'address', 'GPS_Lon', 'GPS_Lat', 'city_id'),
                'City' => array(
                    'fields' => array('id', 'name', 'url_name'),
                ),
            ),
            'Venue' => array(
                'fields' => array('id', 'name', 'slug', 'address', 'GPS_Lon', 'GPS_Lat', 'city_id'),
                'City' => array(
                    'fields' => array('id', 'name', 'url_name')
                )
            ),
            'Schedule' => array(
                'fields' => array('id', 'name'),
                'Date' => array(
                    'fields' => array('start', 'end'),
                    'conditions' => array(
                        'Date.start >=' => $qOpts['start'],
                        'Date.start <=' => $qOpts['end'],
                    ),
                ),
            ),
            'EventType' => array(
                'fields' => array('id', 'name', 'slug'),
            ),
            'EventSubType' => array(
                'fields' => array('id', 'name', 'slug'),
            ),
            'EventSubSubType' => array(
                'fields' => array('id', 'name', 'slug'),
            ),
        ),
        'joins' => array(
            array(
                'table' => $this->Schedule->table,
                'alias' => 'Schedule',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'Schedule.event_id = Event.id',
                ),
            ),
            array(
                'table' => $this->Schedule->Date->table,
                'alias' => 'Date',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'Date.schedule_id = Schedule.id',
                ),
            ),
            array(
                'table' => $this->EventTypesEvent->table,
                'alias' => 'EventTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventTypesEvents.event_id = Event.id',
                ),
            ),
            array(
                'table' => $this->EventSubTypesEvent->table,
                //'table' => 'event_sub_types_events',
                'alias' => 'EventSubTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventSubTypesEvents.event_id = Event.id',
                ),
            ),
            array(
                'table' => $this->EventSubSubTypesEvent->table,
                'alias' => 'EventSubSubTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventSubSubTypesEvents.event_id = Event.id',
                ),
            ),
        ),
        'conditions' => $qOpts['conditions'],
        'limit' => $qOpts['limit'],
        'group' => 'Event.id'
    ));
    return $data;
}

推荐答案

GROUP_CONCAT 来救援！！！长话短说 - 我需要返回带有许多日期的事件(能够查询不同的 HABTM 表) - 但是当我尝试时，我要么得到太多事件(每个日期一个......等)要么我会使用 GROUP BY，而不是获取所有日期.答案...仍然使用 GROUP BY，但使用 GROUP_CONCAT 将日期合并到一个字段中:

GROUP_CONCAT to the rescue!!! Long story short - I needed to return Events with their many Dates (with being able to query against different HABTM tables) - but when I tried, I'd either get way too many events (one for each date...etc) or I'd use GROUP BY, and not get all the dates. The answer... still use GROUP BY, but combine the Dates into a single field using GROUP_CONCAT:

$qOpts['fields'] = array(
        ...
        'GROUP_CONCAT(Date.start, "|", Date.end ORDER BY Date.start ASC SEPARATOR "||") AS EventDates'
    );

我发布了大量代码 - 如果您像我一样遇到问题，请随时使用浏览器.

I'm posting a lot of code - feel free to browser if you got stuck like I did.

我学到的东西:

• 不建议使用包含和连接 - 选择一个并坚持使用 - 这有一段时间是我存在的祸根 - 我会得到一些工作，但随后不使用分页......等.
• 如果需要根据HABTM数据查询，选择join，不包含
• 我的 Join 工作得很好，但我会收到 10 次相同的事件(每个存在的日期 1 次)
• 但是当我尝试 GROUP BY 时，它组合了它们，所以我只有 1 个日期，当我真的需要所有日期时
• GROUP_CONCAT 很棒(以前从未听说过)

希望这对某人有所帮助.随意指出我的代码的任何问题 - 我总是喜欢改进.但是现在，我正在绕圈跳舞，因为它有效！！！现在它可以工作了，我将回去尝试清理像@bfavaretto 提到的那些 OR.

Hope this helps someone. Feel free to point out any issues w/ my code - I always like to improve. But for now, I'm dancing in circles because it WORKS!!! Now that it works, I'm going to go back in and try to clean up those ORs like @bfavaretto mentioned.

    //returns events based on category, subcategory, and start/end datetimes
function getEvents($opts = null) {
    //$opts = limit, start(date), end(date), types, subtypes, subsubtypes, cities, paginate(0,1), venues, excludes(event ids)

    $qOpts['conditions'] = array();

    //order
    $qOpts['order'] = 'Date.start ASC';
    if(isset($opts['order'])) $qOpts['order'] = $opts['order'];

    //dates
    $qOpts['start'] = date('Y-m-d') . ' 00:00:00';
    if(isset($opts['start'])) $qOpts['start'] = $opts['start'];

    //limit
    $qOpts['limit'] = 10;
    if(isset($opts['limit'])) $qOpts['limit'] = $opts['limit'];

    //event excludes (example: when you want "other events at this venue", you need to exclude current event)
    if(isset($opts['excludes'])) {
        if(is_array($opts['excludes'])) {
            foreach($opts['excludes'] as $exclude_id) {
                array_push($qOpts['conditions'], array('Event.id <>' => $exclude_id));
            }
        }
    }

    //approval status conditions
    if(!isset($opts['approval_statuses'])) $opts['approval_statuses'] = array('1'); //default 1 = approved
    if(isset($opts['approval_statuses'])) {
        if(is_array($opts['approval_statuses'])) {
            $approvalStatusesConditions['OR'] = array();
            foreach($opts['approval_statuses'] as $status) {
                array_push($approvalStatusesConditions['OR'], array('Event.approval_status_id' => $status));
            }
            array_push($qOpts['conditions'], $approvalStatusesConditions);
        }
    }

    //date conditions
    $date_conditions = array();
    array_push($qOpts['conditions'], array('Date.start >=' => $qOpts['start']));
    array_push($date_conditions, array('Date.start >=' => $qOpts['start']));

    if(isset($opts['end'])) {
        array_push($qOpts['conditions'], array('Date.start <=' => $opts['end']));
        array_push($date_conditions, array('Date.start <=' => $opts['end']));
    }


    //venues conditions
    if(isset($opts['venues'])) {
        if(is_array($opts['venues'])) {
            $venueConditions['OR'] = array();
            foreach($opts['venues'] as $venue_id) {
                array_push($venueConditions['OR'], array('OR'=>array('Venue.id'=>$venue_id)));
            }
            array_push($qOpts['conditions'], $venueConditions);
        }
    }

    //cities conditions
    if(isset($opts['cities'])) {
        if(is_array($opts['cities'])) {
            $cityConditions['OR'] = array();
            foreach($opts['cities'] as $city_id) {
                array_push($cityConditions['OR'], array('OR'=>array('Venue.city_id'=>$city_id, 'Restaurant.city_id'=>$city_id)));
            }
            array_push($qOpts['conditions'], $cityConditions);
        }
    }

    //event types conditions
    if(isset($opts['event_types'])) {
        if(is_array($opts['event_types'])) {
            $eventTypeConditions['OR'] = array();
            foreach($opts['event_types'] as $event_type_id) {
                array_push($eventTypeConditions['OR'], array('EventTypesEvents.event_type_id' => $event_type_id));
            }
            array_push($qOpts['conditions'], $eventTypeConditions);
        }
    }

    //event sub types conditions
    if(isset($opts['event_sub_types'])) {
        if(is_array($opts['event_sub_types'])) {
            $eventSubTypeConditions['OR'] = array();
            foreach($opts['event_sub_types'] as $event_sub_type_id) {
                array_push($eventSubTypeConditions['OR'], array('EventSubTypesEvents.event_sub_type_id' => $event_sub_type_id));
            }
            array_push($qOpts['conditions'], $eventSubTypeConditions);
        }
    }

    //event sub sub types conditions
    if(isset($opts['event_sub_sub_types'])) {
        if(is_array($opts['event_sub_sub_types'])) {
            $eventSubSubTypeConditions['OR'] = array();
            foreach($opts['event_sub_sub_types'] as $event_sub_sub_type_id) {
                array_push($eventSubSubTypeConditions['OR'], array('EventSubSubTypesEvents.event_sub_sub_type_id' => $event_sub_sub_type_id));
            }
            array_push($qOpts['conditions'], $eventSubSubTypeConditions);
        }
    }


    //joins
    $qOpts['joins'] = array();

    //Restaurants join
    array_push($qOpts['joins'], array(
            'table' => $this->Restaurant->table,
            'alias' => 'Restaurant',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => array(
                'Restaurant.id = Event.restaurant_id',
            ),
        )
    );

    //Venues join
    array_push($qOpts['joins'], array(
            'table' => $this->Venue->table,
            'alias' => 'Venue',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => array(
                'Venue.id = Event.venue_id',
            ),
        )
    );

    //Schedules join
    array_push($qOpts['joins'], array(
            'table' => $this->Schedule->table,
            'alias' => 'Schedule',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => array(
                'Schedule.event_id = Event.id',
            ),
        )
    );

    //Dates join
    array_push($qOpts['joins'], array(
        'table' => $this->Schedule->Date->table,
        'alias' => 'Date',
        'type' => 'INNER',
        'foreignKey' => false,
        'conditions' => array(
            'Date.schedule_id = Schedule.id',
            //$date_conditions
        ),
    ));

    //Uploads join
    array_push($qOpts['joins'], array(
            'table' => $this->Upload->table,
            'alias' => 'Upload',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => array(
                'Upload.event_id = Event.id',
            ),
        )
    );

    //Event types join
    if(isset($opts['event_types'])) {
        if(is_array($opts['event_types'])) {
            array_push($qOpts['joins'], array(
                'table' => $this->EventTypesEvent->table,
                'alias' => 'EventTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventTypesEvents.event_id = Event.id',
                ),
            ));
        }
    }
    if(isset($opts['event_sub_types'])) {
        if(is_array($opts['event_sub_types'])) {
            array_push($qOpts['joins'], array(
                'table' => $this->EventSubTypesEvent->table,
                'alias' => 'EventSubTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventSubTypesEvents.event_id = Event.id',
                ),
            ));
        }
    }
    if(isset($opts['event_sub_sub_types'])) {
        if(is_array($opts['event_sub_sub_types'])) {
            array_push($qOpts['joins'], array(
                'table' => $this->EventSubSubTypesEvent->table,
                'alias' => 'EventSubSubTypesEvents',
                'type' => 'INNER',
                'foreignKey' => false,
                'conditions' => array(
                    'EventSubSubTypesEvents.event_id = Event.id',
                ),
            ));
        }
    }

    $qOpts['fields'] = array(
        'Event.*',
        'Venue.id', 'Venue.slug', 'Venue.name', 'Venue.GPS_Lon', 'Venue.GPS_Lat',
        'Restaurant.id', 'Restaurant.slug', 'Restaurant.name', 'Restaurant.GPS_Lat', 'Restaurant.GPS_Lon',
        'GROUP_CONCAT(Date.start, "|", Date.end ORDER BY Date.start ASC SEPARATOR "||") AS EventDates'
    );

    //group by
    $qOpts['group'] = 'Event.id';

    //you need to set the recursion to -1 for this type of join-search
    $this->recursive = -1;


    $paginate = false;
    if(isset($opts['paginate'])) {
        if($opts['paginate']) {
            $paginate = true;
        }
    }

    //either return the options just created (paginate)
    if($paginate) {
        return $qOpts;

    //or return the events data
    } else {
        $data = $this->find('all', $qOpts);
        return $data;
    }

}