选择与事件 - ＆GT的所有活动;附表＆gt;在CakePHP的开始和结束日期之间的日期 [英] Select All Events with Event->Schedule->Date between start and end dates in CakePHP

查看：99 发布时间：2016/7/19 16:31:20 mysql cakephp associations cakephp-1.3

本文介绍了选择与事件 - ＆GT的所有活动;附表＆gt;在CakePHP的开始和结束日期之间的日期的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

试图找出如何建立在CakePHP中查询在那里我可以选择的X和Y日期（用户输入的日期）之间的所有活动。

问题在于，事件没有日期在它的表。

 事件的hasMany时间表
附表事件的belongsTo计划的hasMany日期
日期安排的belongsTo

事件表：对事件的详细信息 - 名称，位置，说明...等

调度表：开始和结束日期与重复选项

日期表：从数据创建的事件在时间表的实际日期

所以 - 我真的需要选择具有X和Y日期之间至少有一个日期项中的任何活动。

。

我还需要能够与所述事件的数据，以显示日期

修改（修订本）：

我试过这个，但如果日期落在范围内似乎不管被检索日期的事件，但只检索最新信息：

  $这个 - ＆GT;＆事件 -  GT; Behaviors-＆GT;附加（'容纳的'）;
$事件= $这个 - ＆GT;＆事件 -  GT;找到（'所有'，阵列（
    '限制'=大于5，
    秩序= GT;'Event.created DESC'，
    '包含'=＆GT;阵列（
    '计划'=＆GT;阵列（
        '域'=＆GT;阵（）
        '日期'=＆GT;阵列（
            '条件'=＆GT;阵列（
                开始＆gt; ='= GT; $起始日期，
                启动＆LT; ='= GT; $ END_DATE，
                ）
            ）
        ）
    ）
））;

*只是为了澄清 - Date.start和Date.end总是相同的日期 - 他们只是还包括一个时间（两个日期时间字段） - 因此为什么我检查启动对阵双方

。

我已经使用容纳的尝试，我试着取消绑定/ bindModel..etc - 我一定是做错了什么或偏离轨道

东西要记住 - 一旦我弄清楚如何根据日期来获取事件，我还需要在类似事件类型等条件添加更多的 - 不知道这是否会影响到的答案（S）或不

更新：

下面就是我使用的是什么，似乎工作 - 似乎也很丑陋 - 任何想法？

 函数getEvents（$ OPTS = NULL）{
    // $ =选择采用限，启动（日期），端（日期），类型，亚型，subsubtypes，城市    $ qOpts ['条件'] =阵列（）;    //日期
    $ qOpts ['开始'] =日期（'Y-M-D'）。 00:00:00;
    如果（使用isset（$ OPTS ['开始']））$ qOpts ['开始'] = $选择采用['开始'];    $ qOpts ['端'] =日期（'Y-M-D'）。 23:59:59;
    如果（使用isset（$ OPTS ['端']））$ qOpts ['端'] = $选择采用['端'];    //限制
    $ qOpts ['限制'] = 10;
    如果（使用isset（$ OPTS ['限制']））$ qOpts ['限制'] = $选择采用['限制'];    //领域
    // $ qOpts ['字段'] =阵列（'Event.id'，'Event.name'，'Event.slug'，'City.name'，'Date.start'）;
    //如果（使用isset（$ OPTS ['场']））$ qOpts ['域'] = $选择采用['域'];
    //日期条件
    array_push（$ qOpts ['条件']，阵列（
        Date.start＆GT; ==＆GT; $ qOpts ['开始']，
        Date.start＆LT; ==＆GT; $ qOpts ['端']，
    ））;    //城市条件
    如果（使用isset（$ OPTS ['城市']））{
        如果（is_array（$ OPTS ['城市']））{
            $ cityConditions ['或'] =阵列（）;
            的foreach（$选择采用['城市']为$ city_id）{
                array_push（$ cityConditions ['或']，阵列（OR= GT;阵列（'Venue.city_id'= GT; $ city_id，'Restaurant.city_id'=＆GT; $ city_id）））;
            }
            array_push（$ qOpts ['条件']，$ cityConditions）;
        }
    }    //事件类型条件
    // $ OPTS ['event_types'] =阵列（'1'）;
    如果（使用isset（$ OPTS ['event_types']））{
        如果（is_array（$ OPTS ['event_types']））{
            $ eventTypeConditions ['或'] =阵列（）;
            的foreach（$选择采用['event_types']为$ event_type_id）{
                array_push（$ eventTypeConditions ['或']，阵列（'EventTypesEvents.event_type_id'=＆GT; $ event_type_id））;
            }
            array_push（$ qOpts ['条件']，$ eventTypeConditions）;
        }
    }    //事件子类型条件
    如果（使用isset（$ OPTS ['event_sub_types']））{
        如果（is_array（$ OPTS ['event_sub_types']））{
            $ eventSubTypeConditions ['或'] =阵列（）;
            的foreach（$选择采用['event_sub_types']为$ event_sub_type_id）{
                array_push（$ eventSubTypeConditions ['或']，阵列（'EventSubTypesEvents.event_sub_type_id'=＆GT; $ event_sub_type_id））;
            }
            array_push（$ qOpts ['条件']，$ eventSubTypeConditions）;
        }
    }    //事件子子类型条件
    如果（使用isset（$ OPTS ['event_sub_sub_types']））{
        如果（is_array（$ OPTS ['event_sub_sub_types']））{
            $ eventSubSubTypeConditions ['或'] =阵列（）;
            的foreach（$选择采用['event_sub_sub_types']为$ event_sub_sub_type_id）{
                array_push（$ eventSubSubTypeConditions ['或']，阵列（'EventSubSubTypesEvents.event_sub_sub_type_id'=＆GT; $ event_sub_sub_type_id））;
            }
            array_push（$ qOpts ['条件']，$ eventSubSubTypeConditions）;
        }
    }
    $这个 - ＆GT;递归= 2;    $数据= $这个 - ＆GT;找到（'所有'，阵列（
        '包含'=＆GT;阵列（
            '餐厅'=＆GT;阵列（
                '域'=＆GT;阵列（'身份证'，'名'，'塞'，'地址'，'GPS_Lon'，'GPS_Lat'，'city_id'），
                '市'= GT;阵列（
                    '域'=＆GT;阵列（'身份证'，'名'，'URL_NAME'），
                ）
            ）
            '地点'=＆GT;阵列（
                '域'=＆GT;阵列（'身份证'，'名'，'塞'，'地址'，'GPS_Lon'，'GPS_Lat'，'city_id'），
                '市'= GT;阵列（
                    '域'=＆GT;阵列（'身份证'，'名'，'URL_NAME'）
                ）
            ）
            '计划'=＆GT;阵列（
                '域'=＆GT;阵列（'身份证'，'名'），
                '日期'=＆GT;阵列（
                    '域'=＆GT;阵列（'开始'，'结束'），
                    '条件'=＆GT;阵列（
                        Date.start＆GT; ='= GT; $ qOpts ['开始']，
                        Date.start＆LT; ='= GT; $ qOpts ['端']，
                    ）
                ）
            ）
            事件类型'=＆GT;阵列（
                '域'=＆GT;阵列（'身份证'，'名'，'塞'），
            ）
            EventSubType'=＆GT;阵列（
                '域'=＆GT;阵列（'身份证'，'名'，'塞'），
            ）
            EventSubSubType'=＆GT;阵列（
                '域'=＆GT;阵列（'身份证'，'名'，'塞'），
            ）
        ）
        '加入'=＆GT;阵列（
            阵列（
                '表'=＆GT; $这个 - ＆GT;＆附表GT;表，
                别名'=＆GT; '时间表'，
                '型'=＆GT; '内'，
                FOREIGNKEY'=＆GT;假，
                '条件'=＆GT;阵列（
                    Schedule.event_id = Event.id'，
                ）
            ）
            阵列（
                '表'=＆GT; $这个 - ＆GT;＆附表GT;＆日期 -  GT;表，
                别名'=＆GT; '日期'，
                '型'=＆GT; '内'，
                FOREIGNKEY'=＆GT;假，
                '条件'=＆GT;阵列（
                    Date.schedule_id = Schedule.id'，
                ）
            ）
            阵列（
                '表'=＆GT; $这个 - ＆GT; EventTypesEvent-＆GT;表，
                别名'=＆GT; EventTypesEvents'，
                '型'=＆GT; '内'，
                FOREIGNKEY'=＆GT;假，
                '条件'=＆GT;阵列（
                    EventTypesEvents.event_id = Event.id'，
                ）
            ）
            阵列（
                '表'=＆GT; $这个 - ＆GT; EventSubTypesEvent-＆GT;表，
                //'表'=＆GT; event_sub_types_events'，
                别名'=＆GT; EventSubTypesEvents'，
                '型'=＆GT; '内'，
                FOREIGNKEY'=＆GT;假，
                '条件'=＆GT;阵列（
                    EventSubTypesEvents.event_id = Event.id'，
                ）
            ）
            阵列（
                '表'=＆GT; $这个 - ＆GT; EventSubSubTypesEvent-＆GT;表，
                别名'=＆GT; EventSubSubTypesEvents'，
                '型'=＆GT; '内'，
                FOREIGNKEY'=＆GT;假，
                '条件'=＆GT;阵列（
                    EventSubSubTypesEvents.event_id = Event.id'，
                ）
            ）
        ）
        '条件'=＆GT; $ qOpts ['条件']，
        '限制'=＆GT; $ qOpts ['限制']，
        '组'=＆GT; Event.id
    ））;
    返回$的数据;
}

解决方案

GROUP_CONCAT救援！长话短说 - 我需要回到他们的许多事件的日期（以能够查询针对不同HABTM表） - 但是当我尝试，我想要么得到太多的事件（每个日期...等）或我会使用GROUP BY，而不是让所有的日期。答案...仍然使用GROUP BY，但使用GROUP_CONCAT的日期组合成单场：

  $ qOpts ['域'] =阵列（
        ...
        GROUP_CONCAT（Date.start，|，Date.end ORDER BY Date.start ASC分离器||）作为EventDates
    ）;

我张贴了很多code的 - 随意的浏览器，如果你卡住了像我一样

。

事情我了解到：

这不建议使用含有和加入 - 选择一个，并坚持下去 - 这是我存在了位的克星 - 我得到的东西的工作，但后来不瓦特/ pagination..etc等

如果您需要根据HABTM数据进行查询，挑选加盟，不含有

我的联接是工作得很好，但我会10次获得同样的事件在（1对于存在的每个日期）

但是，当我试图GROUP BY，它融会了他们，所以我只拿到1日起，当我真正需要的所有日期

GROUP_CONCAT是惊人的（从来没有听说过）

希望这可以帮助别人。随意点出任何问题瓦特/我的code - 我总是喜欢来改善。但现在，我在圈子里跳舞，因为它的工作原理！现在，它的作品，我要回去，并尝试清理像提到的那些@bfavaretto OR值。

  //根据类别，子类别返回事件和开始/结束日期时间
功能getEvents（$ OPTS = NULL）{
    // $ =选择采用限，启动（日期），端（日期），类型，亚型，subsubtypes，城市，分页（0,1），场地，不包括（事件ID）    $ qOpts ['条件'] =阵列（）;    //订购
    $ qOpts ['秩序'] ='Date.start ASC;
    如果（使用isset（$ OPTS ['秩序']））$ qOpts ['秩序'] = $选择采用['秩序'];    //日期
    $ qOpts ['开始'] =日期（'Y-M-D'）。 00:00:00;
    如果（使用isset（$ OPTS ['开始']））$ qOpts ['开始'] = $选择采用['开始'];    //限制
    $ qOpts ['限制'] = 10;
    如果（使用isset（$ OPTS ['限制']））$ qOpts ['限制'] = $选择采用['限制'];    //事件排除（例如：当你想在这个场地的其他事件中，你需要排除当前的事件）
    如果（使用isset（$ OPTS ['不包括']））{
        如果（is_array（$ OPTS ['不包括']））{
            的foreach（$选择采用['排除']为$ exclude_id）{
                array_push（$ qOpts ['条件']，阵列（'Event.id＆LT;＆GT;'=＆GT; $ exclude_id））;
            }
        }
    }    //审批状态条件
    如果（！使用isset（$ OPTS ['approval_statuses']））$ OPTS ['approval_statuses'] =阵列（'1'）; //默认1 =批准
    如果（使用isset（$ OPTS ['approval_statuses']））{
        如果（is_array（$ OPTS ['approval_statuses']））{
            $ approvalStatusesConditions ['或'] =阵列（）;
            的foreach（$ OPTS ['approval_statuses']为$状态）{
                array_push（$ approvalStatusesConditions ['或']，阵列（'Event.approval_status_id'=＆GT; $状态））;
            }
            array_push（$ qOpts ['条件']，$ approvalStatusesConditions）;
        }
    }    //日期条件
    $ date_conditions =阵列（）;
    array_push（$ qOpts ['条件']，阵列（'Date.start＆GT; ='= GT; $ qOpts ['开始']））;
    array_push（$ date_conditions，阵列（'Date.start＆GT; ='= GT; $ qOpts ['开始']））;    如果（使用isset（$ OPTS ['端']））{
        array_push（$ qOpts ['条件']，阵列（'Date.start＆LT; ='= GT; $ OPTS ['端']））;
        array_push（$ date_conditions，阵列（'Date.start＆LT; ='= GT; $ OPTS ['端']））;
    }
    //场地条件
    如果（使用isset（$ OPTS ['场地']））{
        如果（is_array（$ OPTS ['场地']））{
            $ venueConditions ['或'] =阵列（）;
            的foreach（$选择采用['场地']为$ venue_id）{
                array_push（$ venueConditions ['或']，阵列（OR= GT;阵列（'Venue.id'= GT; venue_id $）））;
            }
            array_push（$ qOpts ['条件']，$ venueConditions）;
        }
    }    //城市条件
    如果（使用isset（$ OPTS ['城市']））{
        如果（is_array（$ OPTS ['城市']））{
            $ cityConditions ['或'] =阵列（）;
            的foreach（$选择采用['城市']为$ city_id）{
                array_push（$ cityConditions ['或']，阵列（OR= GT;阵列（'Venue.city_id'= GT; $ city_id，'Restaurant.city_id'=＆GT; $ city_id）））;
            }
            array_push（$ qOpts ['条件']，$ cityConditions）;
        }
    }    //事件类型条件
    如果（使用isset（$ OPTS ['event_types']））{
        如果（is_array（$ OPTS ['event_types']））{
            $ eventTypeConditions ['或'] =阵列（）;
            的foreach（$选择采用['event_types']为$ event_type_id）{
                array_push（$ eventTypeConditions ['或']，阵列（'EventTypesEvents.event_type_id'=＆GT; $ event_type_id））;
            }
            array_push（$ qOpts ['条件']，$ eventTypeConditions）;
        }
    }    //事件子类型条件
    如果（使用isset（$ OPTS ['event_sub_types']））{
        如果（is_array（$ OPTS ['event_sub_types']））{
            $ eventSubTypeConditions ['或'] =阵列（）;
            的foreach（$选择采用['event_sub_types']为$ event_sub_type_id）{
                array_push（$ eventSubTypeConditions ['或']，阵列（'EventSubTypesEvents.event_sub_type_id'=＆GT; $ event_sub_type_id））;
            }
            array_push（$ qOpts ['条件']，$ eventSubTypeConditions）;
        }
    }    //事件子子类型条件
    如果（使用isset（$ OPTS ['event_sub_sub_types']））{
        如果（is_array（$ OPTS ['event_sub_sub_types']））{
            $ eventSubSubTypeConditions ['或'] =阵列（）;
            的foreach（$选择采用['event_sub_sub_types']为$ event_sub_sub_type_id）{
                array_push（$ eventSubSubTypeConditions ['或']，阵列（'EventSubSubTypesEvents.event_sub_sub_type_id'=＆GT; $ event_sub_sub_type_id））;
            }
            array_push（$ qOpts ['条件']，$ eventSubSubTypeConditions）;
        }
    }
    //加入
    $ qOpts ['连接'] =阵列（）;    //餐厅加盟
    array_push（$ qOpts ['加入']，阵列（
            '表'=＆GT; $这个 - ＆GT;＆餐厅 -  GT;表，
            别名'=＆GT; '餐厅'，
            '型'=＆GT; '剩下'，
            FOREIGNKEY'=＆GT;假，
            '条件'=＆GT;阵列（
                Restaurant.id = Event.restaurant_id'，
            ）
        ）
    ）;    //场馆加入
    array_push（$ qOpts ['加入']，阵列（
            '表'=＆GT; $这个 - ＆GT; Venue-＆GT;表，
            别名'=＆GT; 地点，
            '型'=＆GT; '剩下'，
            FOREIGNKEY'=＆GT;假，
            '条件'=＆GT;阵列（
                Venue.id = Event.venue_id'，
            ）
        ）
    ）;    //时间表加盟
    array_push（$ qOpts ['加入']，阵列（
            '表'=＆GT; $这个 - ＆GT;＆附表GT;表，
            别名'=＆GT; '时间表'，
            '型'=＆GT; '内'，
            FOREIGNKEY'=＆GT;假，
            '条件'=＆GT;阵列（
                Schedule.event_id = Event.id'，
            ）
        ）
    ）;    //加入日期
    array_push（$ qOpts ['加入']，阵列（
        '表'=＆GT; $这个 - ＆GT;＆附表GT;＆日期 -  GT;表，
        别名'=＆GT; '日期'，
        '型'=＆GT; '内'，
        FOREIGNKEY'=＆GT;假，
        '条件'=＆GT;阵列（
            Date.schedule_id = Schedule.id'，
            // $ date_conditions
        ）
    ））;    //上传加盟
    array_push（$ qOpts ['加入']，阵列（
            '表'=＆GT; $这个 - ＆GT; Upload-＆GT;表，
            别名'=＆GT; '上传'，
            '型'=＆GT; '剩下'，
            FOREIGNKEY'=＆GT;假，
            '条件'=＆GT;阵列（
                Upload.event_id = Event.id'，
            ）
        ）
    ）;    //事件类型加入
    如果（使用isset（$ OPTS ['event_types']））{
        如果（is_array（$ OPTS ['event_types']））{
            array_push（$ qOpts ['加入']，阵列（
                '表'=＆GT; $这个 - ＆GT; EventTypesEvent-＆GT;表，
                别名'=＆GT; EventTypesEvents'，
                '型'=＆GT; '内'，
                FOREIGNKEY'=＆GT;假，
                '条件'=＆GT;阵列（
                    EventTypesEvents.event_id = Event.id'，
                ）
            ））;
        }
    }
    如果（使用isset（$ OPTS ['event_sub_types']））{
        如果（is_array（$ OPTS ['event_sub_types']））{
            array_push（$ qOpts ['加入']，阵列（
                '表'=＆GT; $这个 - ＆GT; EventSubTypesEvent-＆GT;表，
                别名'=＆GT; EventSubTypesEvents'，
                '型'=＆GT; '内'，
                FOREIGNKEY'=＆GT;假，
                '条件'=＆GT;阵列（
                    EventSubTypesEvents.event_id = Event.id'，
                ）
            ））;
        }
    }
    如果（使用isset（$ OPTS ['event_sub_sub_types']））{
        如果（is_array（$ OPTS ['event_sub_sub_types']））{
            array_push（$ qOpts ['加入']，阵列（
                '表'=＆GT; $这个 - ＆GT; EventSubSubTypesEvent-＆GT;表，
                别名'=＆GT; EventSubSubTypesEvents'，
                '型'=＆GT; '内'，
                FOREIGNKEY'=＆GT;假，
                '条件'=＆GT;阵列（
                    EventSubSubTypesEvents.event_id = Event.id'，
                ）
            ））;
        }
    }    $ qOpts ['域'] =阵列（
        '事件。*'，
        '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分离器||）作为EventDates
    ）;    //通过...分组
    $ qOpts ['组'] ='Event.id';    //你需要递归设置为-1对于这种类型的加入，搜索
    $这个 - ＆GT;递归= -1;
    $ PAGINATE = FALSE;
    如果（使用isset（$ OPTS ['PAGINATE']））{
        如果（$选择采用['PAGINATE']）{
            $ PAGINATE = TRUE;
        }
    }    //要么返回刚刚创建的选项（分页）
    如果（$ PAGINATE）{
        返回$ qOpts;    //或返回的事件数据
    }其他{
        $数据= $这个 - ＆GT;找到（'所有'，$ qOpts）;
        返回$的数据;
    }}

Trying to figure out how to build a query in CakePHP where I can select all Events that are between X and Y dates (user-entered dates).

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: 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

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.

Edit (REVISED):

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,
                )
            )
        )
    ),
));

*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.

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.

UPDATE:

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 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.

Things I learned:

It's NOT recommended to use contain AND join - pick one and stick with it - this was the bane of my existence for a bit - I'd get something to work, but then not w/ pagination..etc etc.
If you need to query based on HABTM data, pick join, not contain
My Joins were working just fine, but I would get the same event 10 times over (1 for each date that existed)
But when I tried to GROUP BY, it combined them, so I only got 1 date, when I really needed all the dates
GROUP_CONCAT is amazing (had never heard of it before)

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

}

这篇关于选择与事件 - ＆GT的所有活动;附表＆gt;在CakePHP的开始和结束日期之间的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

选择与事件 - ＆GT的所有活动;附表＆gt;在CakePHP的开始和结束日期之间的日期 [英] Select All Events with Event->Schedule->Date between start and end dates in CakePHP

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

选择与事件 - ＆GT的所有活动;附表＆gt;在CakePHP的开始和结束日期之间的日期 [英] Select All Events with Event-&gt;Schedule-&gt;Date between start and end dates in CakePHP

问题描述

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

选择与事件 - ＆GT的所有活动;附表＆gt;在CakePHP的开始和结束日期之间的日期 [英] Select All Events with Event->Schedule->Date between start and end dates in CakePHP

登录关闭