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
– AlBlue, Commented Jun 25, 2016 at 12:22
By using an if statement and only calling these methods in case the condition is true? — ndm
– ndm, Commented Jun 25, 2016 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
– canned, Commented Jun 25, 2016 at 20:04
@ndm: But where can I add the if-statement in the above code? — canned
– canned, Commented Jun 25, 2016 at 20:05

AD7six · Accepted Answer · 2016-06-26 16:49:31Z

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, 2016 at 16:49

answered Jun 26, 2016 at 9:17

AD7six

67.3k14 gold badges123 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

canned Over a year ago

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 Over a year ago

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 Over a year ago

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?

AD7six Over a year ago

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.

canned Over a year ago

Now it works. Thanks, AD7six. Sorry, I'm new to stackoverflow too... :)

|

Collectives™ on Stack Overflow

CakePHP3: Generate DB-Query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related