jrquick jrquick - 3 months ago 9
PHP Question

CakePHP allow searching by field using API?

I am trying to create an API using CakePHP that allows searching. For example:

http://localhost:8765/users/index/?username=admin


Which should return users with usernames equal to 'admin':

users: [
{
id: 3,
username: "admin",
image: "",
firstName: "Jeremy",
lastName: "Quick",
userTypeId: 1,
email: "jrquick@test.com",
groupId: 2
}
]


So far, I have been able to accomplish this with a custom get() in the AppController which checks the $_GET and $_POST array for fields on the model. But the function is getting more and more complicated and verging on hackiness as I add more functionality (range search, collection search, and child table filtering). Is there a better, more CakePHP friendly way of accomplishing this? Whether through pure cakephp or a plugin?

Answer

I haven't found an answer that seems to work exactly how I am wanting, so here is my current get command. It does allow searching by fields, join tables, greater/less than, in array, and like.

If anyone has recommendations to improve I will update my answer.

public function get() {
    $response = new Response();

    $model = $this->loadModel();

    $fields = $this->getFields();
    $joins = $this->getJoins();
    $order = $this->getOrder();
    $params = $this->getParams();
    $limit = $this->getLimit();
    $offset = $this->getOffset();

    $query = $model->find('all', ['fields' => $fields]);
    if (!is_null($joins)) {
        $query->contain($joins);
    }
    if (sizeof($params['equals']) > 0) {
        foreach ($params['equals'] as $equalsKey=>$equalsValue) {
            $query->andWhere([$equalsKey => $equalsValue]);
        }
    }
    if (sizeof($params['or']) > 0) {
        foreach ($params['or'] as $orKey=>$orValue) {
            $query->orWhere([$orKey => $orValue]);
        }
    }
    if (!is_null($order)) {
        $query->order([$order]);
    }
    if (!is_null($limit)) {
        $query->limit($limit);
        if (!is_null($offset)) {
            $query->offset($offset);
        }
    }
    $response->addMessage($model->table(), $query->toArray());

    $response->respond($this);
}

private function getFields() {
    $fields = [];
    if (array_key_exists('fields', $_GET)) {
        $fields = explode(',', $_GET['fields']);
    }

    return $fields;
}

private function getLimit() {
    $limit = null;
    if (array_key_exists('limit', $_GET)) {
        $limit = $_GET['limit'];
    }

    return $limit;
}

private function getJoins() {
    $joins = null;
    if (array_key_exists('joins', $_GET)) {
        $joins = explode(',', $_GET['joins']);
    }

    return $joins;
}

private function getOffset() {
    $offset = null;
    if (array_key_exists('offset', $_GET)) {
        $offset = $_GET['limit'];
    }

    return $offset;
}

private function getOrder() {
    $results = [];

    if (array_key_exists('order', $_GET)) {
        $orders = explode(',', $_GET['order']);

        foreach ($orders as $order) {
            $sign = substr($order, 0, 1);
            $direction = 'ASC';
            if (in_array($sign, ['+', '-'])) {
                if ($sign === '-') {
                    $direction = 'DESC';
                }

                $order = substr($order, 1);
            }

            $result = $order;
            if (strpos($result, '.') === false) {
                $result = $this->loadModel()->alias() . '.' . $order;
            }
            $result = $result . ' ' . $direction;

            $results[] = $result;
        }
    }

    return (sizeof($results) == 0) ? null : implode(',', $results);
}

private function getParams() {
    $params = [
        'equals' => [],
        'or'     => []
    ];

    $parentModel = $this->loadModel();

    $array = array_merge($_GET, $_POST);
    foreach ($array as $field=>$value) {
        $comparisonType = 'equals';
        $operator = substr($field, strlen($field) - 1);
        if (in_array($operator, ['!', '>', '<'])) {
            $field = substr($field, 0, strlen($field) - 1);
            $operator .= '=';
        } else if (in_array($operator, ['|'])) {
            $field = substr($field, 0, strlen($field) - 1);
            $comparisonType = 'or';
            $operator = '=';
        } else if (in_array($operator, ['%'])) {
            $field = substr($field, 0, strlen($field) - 1);
            $operator = 'LIKE';
            $value = '%'.$value.'%';
        } else {
            $operator = '=';
        }

        if ($value == 'null') {
            $operator = (strpos($operator, '!') === false) ? 'IS' : 'IS NOT';
            $value = null;
        }

        $field = str_replace('_', '.', $field);
        if (strpos($field, '.') === false) {
            $alias = $parentModel->alias();
        } else {
            $fieldExplosion = explode('.', $field);
            $alias = $fieldExplosion[0];
            $field = $fieldExplosion[1];
        }

        $model = null;
        if ($parentModel->alias() !== $alias) {
            $association = $parentModel->associations()->get($alias);
            if (!is_null($association)) {
                $model = $this->loadModel($association->className());
            }
        } else {
            $model = $parentModel;
        }

        if (!is_null($model)) {
            if ($model->hasField(rtrim($field, 's')) && !$model->hasField($field)) {
                $field = rtrim($field, 's');
                $value = '(' . $value . ')';
                $operator = ' IN';
            }

            if ($model->hasField($field)) {
                $params[$comparisonType][$alias.'.'.$field . ' ' . $operator] = $value;
            }
        }
    }

    return $params;
}
Comments