[eluser]WanWizard[/eluser]
To add to this: the zip contains a sample application so you can see how it's used.
[eluser]kinglozzer[/eluser]
I'm wondering if what I want to do requires subqueries, or just a rethink of the structure of my code:
I have two models below, Venue and Location. Venue is self-explanatory, with lat, lon, capacity etc. Location is just a table of locations that a user has searched for (to cut down on google maps requests, I store the lat/lon returned).
The problem I'm having is that I want to avoid doing the same query twice. I need it to count the rows (without any limits or offsets) and then also return them after limits and offsets have been applied.
I've got my simplified code below:
Code: $venue = new Venue();
$result['status'] = 'success';
if ($location AND $location != 'any' AND strlen($location) >= 3)
{
$l = new Location();
$l->where('name', $location)->get();
// Either get existing lat/lon if exists, or encode new one
$location = $l->exists() ? $l->where('name', $location)->get(1) : $this->add_gmap_json($location);
if (isset($location->lat))
{
$venue->select('*, (/*math for distance*/) as distance');
$venue->having('distance < 5');
$venue->order_by('distance', 'asc');
$result['location'] = $location;
}
else
{
$result['status'] = 'error';
}
}
if ($capacity AND $capacity != 'any')
{
$venue->where('capacity >=', $capacity);
}
$venue = $venue->get();
$result['num_rows'] = $venue->result_count();
if ($perpage OR $offset)
{
$venue->limit($perpage);
$venue->offset($offset);
}
if ($location instanceof Location)
{
$venue->select('*, (/* math for distance*/) as distance');
$venue->having('distance < 5');
$venue->order_by('distance', 'asc');
}
if ($capacity AND $capacity != 'any')
{
$venue->where('capacity >=', $capacity);
}
$result['results'] = $venue->get();
return $result;
Any way I can avoid doing two database queries here?
Thanks
[eluser]WanWizard[/eluser]
If you want both the limited result and an unlimited count, there is no way you can do that with a single query (unless you fetch everything and apply the limit in code, but that doesn't give you any gain).
There is however no need to run the get() twice.
Build your complete query, then run
Code: $count = $venue->count();
instead of a get.
after that, stick your limit and offset on, and run the get.
[eluser]kinglozzer[/eluser]
[quote author="WanWizard" date="1349798182"]If you want both the limited result and an unlimited count, there is no way you can do that with a single query (unless you fetch everything and apply the limit in code, but that doesn't give you any gain).
There is however no need to run the get() twice.
Build your complete query, then run
Code: $count = $venue->count();
instead of a get.
after that, stick your limit and offset on, and run the get.[/quote]
Okay, I thought as much. Thanks for clearing that up.
[eluser]kinglozzer[/eluser]
[quote author="WanWizard" date="1349798182"]If you want both the limited result and an unlimited count, there is no way you can do that with a single query (unless you fetch everything and apply the limit in code, but that doesn't give you any gain).
There is however no need to run the get() twice.
Build your complete query, then run
Code: $count = $venue->count();
instead of a get.
after that, stick your limit and offset on, and run the get.[/quote]
I can't use the count() function, as I am using a custom select() in order to use my 'having' clause - the count() function overrides my select, to COUNT(*) and I get an unknown column 'distance' error as I am no longer selecting "<my math> as distance".
Is there any way I can use the count() function without having to build my 'select' portion twice? Just for the sake of cleaner code.
Thanks
[eluser]WanWizard[/eluser]
Build your query to the point you need, then clone the object, and run the count on the clone?
[eluser]kinglozzer[/eluser]
[quote author="WanWizard" date="1349870315"]Build your query to the point you need, then clone the object, and run the count on the clone?[/quote]
Ah, I was trying to clone it by doing:
Code: $clone = clone $venue
But then spotted in the DataMapper docs the get_clone() function!
Thanks for your help, DataMapper is fantastic by the way!
[eluser]kinglozzer[/eluser]
Sorry to be a pain, but I suck at SQL so can't even get my head around the theory of what I'm trying to do this time..
This is similar to my venue search, but this time it's an 'event' search. Each event 'has one' venue, and I want to filter events by venues that within 5 miles of the location entered.
The code below works, except it overrides the values in my $event instance. For example, both events and venues have a field 'url_segment', the code below overrides the event url segment with the venue one, and I can't access the venue by doing $event->venue->name, I have to do $event->name.
What am I doing wrong here?
Code: $event = new Event();
$result['status'] = 'success';
if ($location AND $location != 'any' AND strlen($location) >= 3)
{
$l = new Location();
$l->where('name', $location)->get();
// Either get existing lat/lon if exists, or encode new one
$location = $l->exists() ? $l->where('name', $location)->get(1) : $this->add_gmap_json($location);
if (isset($location->lat))
{
$venue = new Venue();
$event->select_related($venue, '*, (((acos(sin(('.$location->lat.' *pi()/180))*sin((`lat`*pi()/180))+cos(('.$location->lat.'*pi()/180))*cos((`lat`*pi()/180))*cos((('.$location->lon.'-`lon`)*pi()/180))))*180/pi())*60*1.1515) as distance');
$event->having('distance < 5');
$result['loc'] = $location;
}
else
{
$result['status'] = 'error';
}
}
/* more filtering */
$clone = $event->get_clone();
$clone->get();
$result['num_rows'] = $clone->result_count();
if ($limit OR $offset)
{
$event->limit($limit);
$event->offset($offset);
}
$result['results'] = $event->get();
return $result;
Thanks again for your help!
[eluser]WanWizard[/eluser]
Try to rewrite it using include_related().
It allows you to prefix the column names of the joined table (by default with the model name) to avoid column name collisions...
[eluser]kinglozzer[/eluser]
[quote author="WanWizard" date="1349882520"]Try to rewrite it using include_related().
It allows you to prefix the column names of the joined table (by default with the model name) to avoid column name collisions...[/quote]
Gah! It was staring me right in the face the whole time, I'd been getting sql errors because my select statement included a second *!
You rock, thanks
|
