mysql – 在CakePHP中选择所有事件 – 事件 – >计划 – >开始日期和结束日期之间的日期

试图找出如何在CakePHP中构建查询,我可以在其中选择X和Y日期之间的所有事件(用户输入的日期).

问题在于事件的表中没有日期.

Event hasMany Schedule
Schedule belongsTo Event

Schedule hasMany Date
Date belongsTo Schedule

>活动表:活动详情 – 名称,地点,描述等
>计划表:重复选项的开始和结束日期
>日期表:根据计划中的数据创建的事件的实际日期

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

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

编辑(修订):

我试过这个,但它似乎是检索事件而不管日期,但只检索日期信息,如果日期在范围内:

$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总是相同的日期 – 它们还包括一个时间(两个日期时间字段) – 因此我为什么要检查两者的“开始”.

我已经尝试过使用可包含的,我尝试过unbind / bindModel..etc – 我必须做错事或偏离轨道.

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

更新:

这是我正在使用的似乎工作 – 似乎也很丑 – 任何想法?:

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

解决方法:

在这种情况下,我倾向于不使用Cake的关联或Containable,并自己创建连接:

$events = $this->Event->find('all', array(
    'joins'=>array(
        array(
            'table' => $this->Schedule->table, 
            'alias' => 'Schedule', 
            'type' => 'INNER', 
            'foreignKey' => false,
            'conditions'=> array(
                'Schedule.event_id = Event.id',
            ),
        ),
        array(
            'table' => $this->Date->table, 
            'alias' => 'Date', 
            'type' => 'INNER', 
            'foreignKey' => false,
            'conditions'=> array(
                'Date.schedule_id = Schedule.id',
            ),
        ),
    ),
    'conditions'=>array(
        'Date.start >=' => $start_date,
        'Date.start <=' => $end_date,
    ),
    'order'=>'Event.created DESC',
    'limit'=>5
));

它有点粗,但会产生我想要的确切查询.

UPDATE

让我们分解你的代码,看看我们可以改进它的地方.第一部分是寻找的准备.我已经重写了你的代码,试图缩短它,这就是我提出的:

// Default options go here
$defaultOpts = array(
    'start' => date('Y-m-d') . ' 00:00:00',
    'end' => date('Y-m-d') . ' 23:59:59',
    'limit' => 10
)

// Use default options if nothing is passed, otherwise merge passed options with defaults
$opts = is_array($opts) ? array_merge($defaultOpts, $opts) : $defaultOpts;

// Initialize array to hold query conditions
$conditions = array();

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

//cities conditions
if(isset($opts['cities']) && is_array($opts['cities'])) {
    $conditions['OR'] = array();
    $conditions['OR'][] = array('Venue.city_id'=>$opts['cities']);
    $conditions['OR'][] = array('Restaurant.city_id'=>$opts['cities']);
}

//event types conditions
//$opts['event_types'] = array('1');
if(isset($opts['event_types']) && is_array($opts['event_types'])) {
    $conditions[] = 'EventTypesEvents.event_type_id' => $opts['event_types']
}

//event sub types conditions
if(isset($opts['event_sub_types']) && is_array($opts['event_sub_types'])) {
    $conditions[] = 'EventSubTypesEvents.event_sub_type_id' => $opts['event_sub_types']
}

//event sub sub types conditions
if(isset($opts['event_sub_types']) && is_array($opts['event_sub_sub_types'])) {
    $conditions[] = 'EventSubSubTypesEvents.event_sub_sub_type_id' => $opts['event_sub_sub_types']
}

请注意,我删除了大部分的OR.那是因为您可以在条件中将数组作为值传递,而Cake将使它成为SQL查询中的IN(…)语句.例如:’Model.field’=> array(1,2,3)生成’Model.field IN(1,2,3)’.这就像OR一样,但需要的代码更少.因此,上面的代码块与您的代码完全相同,但它更短.

现在是复杂的部分,发现本身.

通常我会建议单独使用强制连接,不使用Containable,并使用’recursive’=> false.我相信这通常是处理复杂发现的最佳方式.使用Associations和Containable,Cake会针对数据库运行多个SQL查询(每个Model /表一个查询),这往往效率低下.此外,Containable并不总是返回预期的结果(正如您在尝试时所注意到的那样).

但由于在您的情况下涉及四个复杂的关联,可能混合方法将是理想的解决方案 – 否则,清理重复数据将太复杂. (4个复杂的关联是:事件有很多日期[通过事件有多个时间表,时间表有多个日期],事件HABTM事件类型,事件HABTM事件类型,事件HABTM事件子类型子事件).因此,我们可以让Cake处理EventType,EventSubType和EventSubSubType的数据检索,避免重复过多.

所以这就是我的建议:使用连接进行所有必需的过滤,但不要在字段中包含Date和[Sub [Sub]]类型.由于您具有模型关联,Cake将自动对DB运行额外查询以获取这些数据位.不需要包含.

编码:

// We already fetch the data from these 2 models through
// joins + fields, so we can unbind them for the next find,
// avoiding extra unnecessary queries. 
$this->unbindModel(array('belongsTo'=>array('Restaurant', 'Venue'));

$data = $this->find('all', array(
    // The other fields required will be added by Cake later
    'fields' => "
        Event.*, 
        Restaurant.id, Restaurant.name, Restaurant.slug, Restaurant.address, Restaurant.GPS_Lon, Restaurant.GPS_Lat, Restaurant.city_id,
        Venue.id, Venue.name, Venue.slug, Venue.address, Venue.GPS_Lon, Venue.GPS_Lat, Venue.city_id,
        City.id, City.name, City.url_name
    ",  
    'joins' => array(
        array(
            'table' => $this->Schedule->table,
            'alias' => 'Schedule',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => 'Schedule.event_id = Event.id',
        ),
        array(
            'table' => $this->Schedule->Date->table,
            'alias' => 'Date',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => 'Date.schedule_id = Schedule.id',
        ),
        array(
            'table' => $this->EventTypesEvent->table,
            'alias' => 'EventTypesEvents',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => 'EventTypesEvents.event_id = Event.id',
        ),
        array(
            'table' => $this->EventSubSubTypesEvent->table,
            'alias' => 'EventSubSubTypesEvents',
            'type' => 'INNER',
            'foreignKey' => false,
            'conditions' => 'EventSubSubTypesEvents.event_id = Event.id',
        ),
        array(
            'table' => $this->Restaurant->table,
            'alias' => 'Restaurant',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => 'Event.restaurant_id = Restaurant.id',
        ),
        array(
            'table' => $this->City->table,
            'alias' => 'RestaurantCity',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => 'Restaurant.city_id = city.id',
        ),
        array(
            'table' => $this->Venue->table,
            'alias' => 'Venue',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => 'Event.venue_id = Venue.id',
        ),
        array(
            'table' => $this->City->table,
            'alias' => 'VenueCity',
            'type' => 'LEFT',
            'foreignKey' => false,
            'conditions' => 'Venue.city_id = city.id',
        ),
    ),
    'conditions' => $conditions,
    'limit' => $opts['limit'],
    'recursive' => 2
));

我们删除了包含,以及Cake正在运行的一些额外查询.大多数联接都是INNER类型.这意味着在连接中涉及的两个表上必须至少存在一条记录,否则您将得到的结果会比您预期的要少.我假设每个事件都发生在一个餐厅或一个地点,但不是两个,这就是为什么我使用LEFT表示那些表(和城市).如果连接中使用的某些字段是可选的,则应在相关联接上使用LEFT而不是INNER.

如果我们在这里使用’recursive’=> false,我们仍然会得到正确的事件,并且没有数据重复,但是日期和[Sub [Sub]]类型将会丢失.通过2级递归,Cake将自动循环返回事件,并且对于每个事件,它将运行必要的查询以获取关联的模型数据.

这几乎就是你在做什么,但没有包含,并且有一些额外的调整.我知道它仍然是一个冗长,丑陋和无聊的代码片段,但毕竟有13个数据库表涉及…

这是所有未经测试的代码,但我相信它应该可行.

上一篇:mysql-了解Sequelize中的关联


下一篇:CakePHP关联错误:在非对象上调用成员函数find()