This problem is driving me nuts.
It appears that cloning a query doesn't work as expected since my last composer update.
The weird thing is that if I don't perform ->get()on the first query clone, the second query executes fine. From the moment I perform the ->geton both query clones, I receive an error: General error: 2031 The clone should create a deep copy of the original query, but something doesn't seem right.
Any ideas? Here is my query (it looks more complex than it is, and the query itself is fine):
//Query dates
$query_onetime = clone $query;
$query_onetime = $query_onetime->join('events_dates', function($join) use ($input_date_start, $input_date_end){
$join->on('events.id', '=', 'events_dates.event_id');
$join->where('events_dates.start_date', "<=", $input_date_end);
$join->where('events_dates.end_date', '>=', $input_date_start);
});
//Select fields
$events_onetime = $query_onetime->select('events.id AS id', 'events.name AS name', 'event_categories.id as category_id', 'event_categories.category as category',
'event_subcategories.id as subcategory_id', 'event_subcategories.subcategory as subcategory',
'short_description', 'time_description',
'price_description', 'nr_going', 'nr_checkedin', 'homepage', 'fbpage', 'profile_pic',
'places.id AS place_id', 'places.name AS place_name', 'lat', 'lng', 'address')
->groupBy('events.id')
->get();
if(isset($data["include_recurrent"]) && $data["include_recurrent"]){
//Query recurrent dates
$query_recurrent = clone $query;
$query_recurrent = $query_recurrent->join('events_dates_recurrent', 'events.id', '=', 'events_dates_recurrent.event_id')
->where(function($join) use ($input_date_start, $input_date_end) {
//Create a dynamic query to get all recurrent dates within the input time interval
$query_string = "ABS(DATEDIFF('" . $input_date_start . "', CAST(events_dates_recurrent.start_date AS DATE)) % events_dates_recurrent.repeat_interval) = 0";
$temp_date_start = $input_date_start;
while(strtotime($temp_date_start) < strtotime($input_date_end)){
$temp_date_start = date('Y-m-d', strtotime($temp_date_start . " +1 day"));
//Create a raw query string
$query_string = $query_string . " OR ABS(DATEDIFF('" . $temp_date_start . "', CAST(events_dates_recurrent.start_date AS DATE)) % events_dates_recurrent.repeat_interval) = 0";
}
$join->whereRaw($query_string);
});
//Select fields
$events_recurrent = $query_recurrent->select('events.id AS id', 'events.name AS name', 'event_categories.id as category_id', 'event_categories.category as category',
'event_subcategories.id as subcategory_id', 'event_subcategories.subcategory as subcategory',
'short_description', 'time_description',
'price_description', 'nr_going', 'nr_checkedin', 'homepage', 'fbpage', 'profile_pic',
'places.id AS place_id', 'places.name AS place_name', 'lat', 'lng', 'address')
->groupBy('events.id')
->get();
$events = array_merge($events_onetime, $events_recurrent);
EDIT: for info on request, here is the full query.
I avoided it because it is pretty long.
// Queries events based on map bounds, category and date
$query = DB::table('events')
->join('places', function($join) use ($data){
$join->on('events.place_id', '=', 'places.id')
->where('places.lat', '>', $data['sw_lat'])
->where('places.lat', '<', $data['ne_lat'])
->where('places.lng', '>', $data['sw_lng'])
->where('places.lng', '<', $data['ne_lng']);
})->join('event_categories', function($join) use ($data){
$join->on('events.category_id', '=', 'event_categories.id');
});
// The category id is optional
if(isset($data["category_id"])){
$query = $query->where('event_categories.id', '=', $data['category_id']);
}
//Query subcategory
$query = $query->leftJoin('event_subcategories', function($join) use ($data){
$join->on('events.subcategory_id', "=", "event_subcategories.id");
});
//Query keywords
$query = $query->join('events_keywords', 'events.id', '=', 'events_keywords.event_id');
//Reverse date format
$input_date_start = date("Y-m-d", strtotime($data["date_start"]));
$input_date_end = date("Y-m-d", strtotime($data["date_end"]));
//Query dates
$query_onetime = clone $query;
$query_onetime = $query_onetime->join('events_dates', function($join) use ($input_date_start, $input_date_end){
$join->on('events.id', '=', 'events_dates.event_id');
$join->where('events_dates.start_date', "<=", $input_date_end);
$join->where('events_dates.end_date', '>=', $input_date_start);
});
//Select fields
$events_onetime = $query_onetime->select('events.id AS id', 'events.name AS name', 'event_categories.id as category_id', 'event_categories.category as category',
'event_subcategories.id as subcategory_id', 'event_subcategories.subcategory as subcategory',
'short_description', 'time_description',
'price_description', 'nr_going', 'nr_checkedin', 'homepage', 'fbpage', 'profile_pic',
'places.id AS place_id', 'places.name AS place_name', 'lat', 'lng', 'address')
->groupBy('events.id')
->get();
foreach($events_onetime as $event){
$temp_event = EventModel::find($event->id);
$event->keywords = $temp_event->keywords;
}
if(isset($data["include_recurrent"]) && $data["include_recurrent"]){
//Query recurrent dates
$query_recurrent = clone $query;
$query_recurrent = $query_recurrent->join('events_dates_recurrent', 'events.id', '=', 'events_dates_recurrent.event_id')
->where(function($join) use ($input_date_start, $input_date_end) {
//Create a dynamic query to get all recurrent dates within the input time interval
$query_string = "ABS(DATEDIFF('" . $input_date_start . "', CAST(events_dates_recurrent.start_date AS DATE)) % events_dates_recurrent.repeat_interval) = 0";
$temp_date_start = $input_date_start;
while(strtotime($temp_date_start) < strtotime($input_date_end)){
$temp_date_start = date('Y-m-d', strtotime($temp_date_start . " +1 day"));
//Create a raw query string
$query_string = $query_string . " OR ABS(DATEDIFF('" . $temp_date_start . "', CAST(events_dates_recurrent.start_date AS DATE)) % events_dates_recurrent.repeat_interval) = 0";
}
$join->whereRaw($query_string);
});
//Select fields
$events_recurrent = $query_recurrent->select('events.id AS id', 'events.name AS name', 'event_categories.id as category_id', 'event_categories.category as category',
'event_subcategories.id as subcategory_id', 'event_subcategories.subcategory as subcategory',
'short_description', 'time_description',
'price_description', 'nr_going', 'nr_checkedin', 'homepage', 'fbpage', 'profile_pic',
'places.id AS place_id', 'places.name AS place_name', 'lat', 'lng', 'address')
->groupBy('events.id')
->get();
//At this point we just have the events, but we also need the keywords per event.
foreach($events_recurrent as $event){
$temp_event = EventModel::find($event->id);
$event->keywords = $temp_event->keywords;
}
$events = array_merge($events_onetime, $events_recurrent);
} else {
//Else return only the non-recurrent events
$events = $events_onetime;
}
return $events;
}
Your $query object is an instance of Illuminate\Database\Eloquent\Builder and it maintains a reference to a Illuminate\Database\Query\Builder which holds the actual query. According to the PHP Docs, the clone keyword performs a shallow copy of an object, meaning that references are copied as references. Thus, $query and $query_onetime both maintain a reference to the same Illuminate\Database\Query\Builder instance and changes to one affect the other.
This issue with needing to clone the underlying query has been fixed in Laravel 4.1, but if you don't want to upgrade you can try this workaround:
$query_onetime = clone $query;
$query_onetime->setQuery(clone $query->getQuery());
// Any additional joins specific to $query_onetime