CakePHP3: Generate DB-Query

Question

I'm new to CakePHP and I would like to query locations in my DB depending on Google Maps Lat & Lng. How can I add the two andWhere statements only, if $params['bounds'] is true?

$params = [
    'bounds' => 1,
    'swLat' => ...,
    'swLng' => ...,
    'neLat' => ...,
    'neLng' => ...
];

$locations = $this->Locations
    ->find()
    ->select(['id', 'name', 'lat', 'lng'])
    ->where(['live' => 1])
    ->andWhere(function ($exp, $q) {
          return $exp->between('lat', $params['swLat'], $params['neLat']);
    })
    ->andWhere(function ($exp, $q) {
          return $exp->between('lng', $params['swLng'], $params['neLng']);
    })
    ->order(['name' => 'ASC']);

If you surround code in-line with backticks, they will be represented in code font. Also, no need to add thanks or your name to the bottom of the question. — AlBlue, Jun 25 '16 at 12:22
By using an `if` statement and only calling these methods in case the condition is true? — ndm, Jun 25 '16 at 15:45
@AlBlue: Sorry, I've added my name with a warm thank you for answers below the code, but strangely it's not visible in my question. Perhaps I messed up someting with the code block. — canned, Jun 25 '16 at 20:04
@ndm: But where can I add the if-statement in the above code? — canned, Jun 25 '16 at 20:05

AD7six · Answer 1 · 2016-06-26T16:49:31.813

1

How can I add the two andWhere statements only, if $params['bounds'] is true?

The query methods are intended to be used (as demonstrated by the question) using a fluent interface; this means each method returns the $this object.

So to add conditional logic, first do the unconditional calls:

$locations = $this->Locations->find()
    ->select(['id', 'name', 'lat', 'lng'])
    ->where(['live' => 1])
    ->order(['name' => 'ASC']);

And then apply whatever other calls you wish:

if (!empty($params['bounds'])) {
    $locations
        ->andWhere(function ($exp, $q) use ($params) {
          return $exp
              ->between('lat', $params['swLat'], $params['neLat']);
              ->between('lng', $params['swLng'], $params['neLng']);
        })
}

Note that for $params to be accessible inside the closure passed to andWhere - it's necessary to use the use language construct. Unlike some languages (most notably javascript) functions do not automatically have access to variables in the parent scope.

edited Jun 26 '16 at 16:49

answered Jun 26 '16 at 09:17

AD7six

63,116
12
91
123

Thanks a lot. Ok, I understand. But why does `$params['swLat']` etc. inside the addWhere-function return NULL? Or in other words: If I am inside the addWhere-function, how can I access a variable which is outside definied? – canned Jun 26 '16 at 16:12
Thanks for your quick answer. Hmm, you see, I'm unexperienced with PHP and new to CakePHP and OOP. Normally I query all locations inside the DB but if the user choses the option "only inside the viewport" (Google Maps API) I would like to display only locations inside the viewport. Is there a possibility to access the variable from the parent scope? Or how would you build the query? – canned Jun 26 '16 at 16:36
If I replace `$params['swLat']` with `$this->request->data['swLat']` it works but if I do so, how can I validate the data before insert into the query? – canned Jun 26 '16 at 16:43
I have updated the answer to clarify, in future if there's a problem with the code in the question (that's not directly related to what you're asking), _please mention it_. – AD7six Jun 26 '16 at 16:44
Now it works. Thanks, AD7six. Sorry, I'm new to stackoverflow too... :) – canned Jun 26 '16 at 16:48
please see http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – AD7six Jun 26 '16 at 16:52

CakePHP3: Generate DB-Query

1 Answers1