CODEWITHSUNDEEP
phpcakephpcakephp-2.0cakephp-2.1
Loolooii
Loolooii

Reputation: 9200

How to search for different models at the same time in CakePHP?

I'm looking for the easiest way to search in different models at the same time. I have 3 models: Game, Movie and Book and I want to be able to search for game, movie or book title in one query. Does anyone know an easy solution? Is this an easy task without use of plugins?

Thanks

Upvotes: 1

Views: 142

Answers (3)

Pádraig Galvin
Pádraig Galvin

Reputation: 1175

Include the following in any model (lets say it's Media for this example):

public function search($query) {
    $query = Sanitize::clean($query);
    $result = $this->query("
        (SELECT id, name, 'book' AS type FROM books WHERE name LIKE '%$query%')
        UNION ALL
        (SELECT id, name, 'movie' AS type FROM movies WHERE name LIKE '%$query%')
        UNION ALL
        (SELECT id, name, 'game' AS type FROM games WHERE name LIKE '%$query%')
    ");
    $result = Hash::extract($result, "{n}.{n}");
    return $result;
}

The 'book' AS type part can be removed if you have some other way of identifying the element type.

Make sure you add App::uses('Sanitize', 'Utility'); in your model file, before the class.

In the Media controller you can use:

$result = $this->Media->search($query);

Which returns something like if the query is "ca":

array(
    0 => array(
        'id' => '12',
        'name' => 'Carly',
        'type' => 'book'
    ),
    1 => array(
        'id' => '19',
        'name' => 'Camden',
        'type' => 'book'
    ),
    2 => array(
        'id' => '20',
        'name' => 'Carter',
        'type' => 'movie'
    )
);

Upvotes: 2

joshua.paling
joshua.paling

Reputation: 13952

Just query one Model, then the next, then the next.

If the models aren't related to each other, there's no way to return results from all of them in a single query - that's a limitation of SQL, not CakePHP (EDIT - wrong! you can using the UNION operator). And there's certainly no way to make a call to three models in one line or anything.

So, just make three separate queries, then do whatever you want with the results. You could display them separately, or merge them all into one array and re-sort them, then display them together - whatever tickles you.

EDIT (after my answer had already been accepted as correct!):

As @xgalvin mentioned, you can join multiple tables in 1 statement using the UNION operator. So, I was wrong. But unless the different tables are very similar, then the UNION operator probably isn't a good fit and you're still better off making 3 queries. If all you want is id, name and type, then @xgalvin's answer is better than mine.

Upvotes: 2

Paulo Rodrigues
Paulo Rodrigues

Reputation: 5303

Without relationship between them, in one find() statement? No.

You can make something like this:

$models = array('Game', 'Movie', 'Book');
$search = 'some title';
$results = array();

foreach($models as $model) {
    $this->loadModel($model);
    $results[$model] = $this->{$model}->find('all', array('conditions' => array($model . '.title' => $search)));
}

Upvotes: 2

Related Questions